Day of Week in Query ?

  • Permalink
  • submit to reddit
  • Email
  • Follow


Hi, any Query/400 experts out there can tell me how I can select on
day of week.

I need to query a file that has a date in it but only extract records
where the date falls on a Saturday. Any ideas how to achieve this?

TIA.
0
Reply gjchap99 (6) 1/26/2004 5:39:31 PM

See related articles to this posting


Jack Altradmon wrote:

> Hi, any Query/400 experts out there can tell me how I can select on
> day of week.
> 
> I need to query a file that has a date in it but only extract records
> where the date falls on a Saturday. Any ideas how to achieve this?
> 

Would the DAYOFWEEK function do what you need?
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/db2/rbafzmstscale.htm

--
Karl Hanson

0
Reply kchanson (271) 1/26/2004 5:53:50 PM

Jack Altradmon wrote:
> Hi, any Query/400 experts out there can tell me how I can select on
> day of week.
> 
> I need to query a file that has a date in it but only extract records
> where the date falls on a Saturday. Any ideas how to achieve this?

There isn't any way I can find in Query/400 to do this 
dirctly.  In SQL, however, there is a DayOfWeek 
function that returns a value of 1-7.  Assuming the 
table has date fields that are in a recognized date 
format, could do this:

First, create a view over the table you want to query:

    create view mylib/myview
	(col1,col2,...,genday) as
    select col1,col2,...,dayofweek(datecol) genday
      from mylib/mytable

col1, col2, etc. are existing columns in the underlying 
table; genday is the name of a column to represent the 
dayofweek value the view.  You need to specify the 
column list because you aren't specifying a simple
    select * ....

from the table.

Next, create your query, and in the selection screen, 
specify

	genday		eq	7

That will give you data only for Saturdays.

0
Reply jonball (677) 1/26/2004 6:45:15 PM

I have had a look to see if Zeller's Congruence can be written in Query/400
but there is a problem with the subtracting a year if the month is January
or February.  I don't know if there is any way round that.

Peter Kinsman

"Jonathan Ball" <jonball@whitehouse.not> wrote in message
news:%edRb.27638$1e.5080@newsread2.news.pas.earthlink.net...
> Jack Altradmon wrote:
> > Hi, any Query/400 experts out there can tell me how I can select on
> > day of week.
> >
> > I need to query a file that has a date in it but only extract records
> > where the date falls on a Saturday. Any ideas how to achieve this?
>
> There isn't any way I can find in Query/400 to do this
> dirctly.  In SQL, however, there is a DayOfWeek
> function that returns a value of 1-7.  Assuming the
> table has date fields that are in a recognized date
> format, could do this:
>
> First, create a view over the table you want to query:
>
>     create view mylib/myview
> (col1,col2,...,genday) as
>     select col1,col2,...,dayofweek(datecol) genday
>       from mylib/mytable
>
> col1, col2, etc. are existing columns in the underlying
> table; genday is the name of a column to represent the
> dayofweek value the view.  You need to specify the
> column list because you aren't specifying a simple
>     select * ....
>
> from the table.
>
> Next, create your query, and in the selection screen,
> specify
>
> genday eq 7
>
> That will give you data only for Saturdays.
>


0
Reply peter8622 (231) 1/27/2004 6:08:25 PM
comp.sys.ibm.as400.misc 9046 articles. 12 followers. Post

3 Replies
571 Views

Similar Articles

[PageSpeed] 27


  • Permalink
  • submit to reddit
  • Email
  • Follow


Reply:

Similar Artilces:

Computing Probabilities for Day of Week, Hour of Day, and Day*Hour
Dear SAS-L I've spent weeks - and written hundreds of pages of program code - on the following problem. Still not licked. The problem is probably due to the fact that I am an amateur and don't know how to routinize repetitive code. Here's the deal. I have a big dataset - 87,000 cases - each of which concern information from victims on a burglary suffered in 2002. Since burglars tend to strike when residents are out of the house, the dataset contains "From Times" and "To Times," which represent the date and time people told police that they left their home, a...

Re: Computing Probabilities for Day of Week, Hour of Day, and Day*Hour
I think Harry's on the right track. Here are some additional thoughts. Use the DHMS function to create datetime values for your start and end points. The H and M arguments will be zero and your fractional times should be multiplied by 24x60x60 to derive the S argument. Loop from the start to end datetimes in hourly increments. Include an OUTPUT statement to generate hourly observations with datetimes and the fractions. INTCK and INTNX functions may be handy. Create additional variables using functions like WEEKDAY and YEAR to set up all of the granularities of interest (day of week, ISO ...

Re: Computing Probabilities for Day of Week, Hour of Day, and Day *Hour
Dave: Does this give the results you're looking for ( sorry, percentages rather than fractions )? Something tells me PROC SUMMARY on the 'days' and 'hours' dataset ( once the inverted totalhours and totaldays are calculated ) might be a more concise solution ( i.e. rather than the FREQ / TRANSPOSE ). Have to run, why don't you play with it? Output follows. data burg; input start datetime18. end datetime18.; n = put(_n_,1.); cards; 23jul2003:13:00:00 23jul2003:13:05:00 23jul2003:19:30:00 24jul2003:01:05:00 25jul2003:19:30:00 26jul2003:19:30:00 27jul20...

Re: Computing Probabilities for Day of Week, Hour of Day, and Day *Hour #3
Correction: Note that day*hour stuff you're looking for will involve a similar approach, looping through the hours, but recording on which day of the week they fall. data hours ( keep = start end n hour: totalhours ) days ( keep = start end n day: totaldays ) ; set burg; array hours{0:23} ; array days{7} ; /* Initialize arrays */ do _i = lbound(hours) to hbound(hours); hours(_i) = .; end; do _i = lbound(days) to hbound(days); days(_i) = .; end; _i = end - start; totalhours = intck('hour',start,end) + 1; hours_mult = 1 / totalhours ...

Re: Computing Probabilities for Day of Week, Hour of Day, and Day *Hour #2
Simpler solution, one datastep. data burg; input start datetime18. end datetime18.; n = put(_n_,1.); cards; 23jul2003:13:00:00 23jul2003:13:05:00 23jul2003:19:30:00 24jul2003:01:05:00 25jul2003:19:30:00 26jul2003:19:30:00 27jul2003:08:00:00 30jul2003:19:10:00 run; options nocenter; title 'Source Data'; proc print data=burg; format _numeric_ datetime18.; run; data hours ( keep = start end n hour: totalhours ) days ( keep = start end n day: totaldays ) ; set burg; array hours{0:23} ; array days{7} ; /* Initialize arrays */ do _i = lbound(hours)...

Re: Computing Probabilities for Day of Week, Hour of Day, and Day*Hour #2
Here's some tested code. First generate some test data: data demo; drop date n; do date = '01jan2002'd to '31dec2004'd; do n = 1 to 200*ranuni(1); FromDate = date - round(30*abs(rannor(1))/50); ToDate = date + round(30*abs(rannor(1))/50); FromTime = ranuni(1); ToTime = ranuni(1); if FromDate<ToDate or FromTime<ToTime then output; end; end; format FromDate ToDate date9.; run; The dataset size can be altered by changing the date range in the outer DO or the factor 200 in the inner DO, but ...

Re: Computing Probabilities for Day of Week, Hour of Day, and Day*Hour #3
Dave's response to Harry and me was also addressed to SAS-L, but for some reason never made it into distribution. See my comments inline. >>> "Dave Sorensen" <Dave.Sorensen@jur.ku.dk> 01/06/04 09:57AM >>> >Dear Howard and Harry, > > >I found a great XMas gift in my email box upon returning from the >holidays. Howard's post of XMas Eve, Dec 24. > >I never really got to go though your code before XMas, Harry, and upon >finding Howard's "tested" code when I returned from the holidays, I >tried it and have found th...

calculating days in a week without week-ends
I have written an application that can calculate the days between 2 dates but I want to be able to calculate it without the week-ends as well as with. What code is required to calculate without the weekends. I have used MOD and DIV for the calculations. I would be very grateful for any help on this. many thanks Stephen On 25 Jul 2004 11:22:31 -0700, support@sjweeks.co.uk (Stephen Weeks) wrote: >I have written an application that can calculate the days between 2 >dates but I want to be able to calculate it without the week-ends as >well as with. > >What co...

re-order days of week in weekly view
Hi does anyone know of an app or fix to the calendar app allowing one to re-order the days of the week so that the first day displayed on the left side of the screen is Sunday and the last day is Saturday? Is this a feature available in a third party app? On Thu, 23 Sep 2004 19:59:59 GMT, Amphora had this to say... > Hi does anyone know of an app or fix to the calendar app allowing one to > re-order the days of the week so that the first day displayed on the left > side of the screen is Sunday and the last day is Saturday? > Is this a feature available in a third p...

How to write a query for each days sales, even if no sales on that day?
Hi, I'm using MySQL 5.0. I have three tables -- SALES_PER_DAY, CALENDAR_DAYS, and USERS, which contain SALES_PER_DAY --------------------------- D DATE, USER_ID INTEGER, SALES FLOAT UNSIGNED PRIMARY KEY (D, USER_ID) CALENDAR_DAYS ---------------------------- D DATE USERS ----------- USER_ID INTEGER GROUP_ID INTEGER There is not necessarily sales for each day. The "CALENDAR_DAYS" table contains one row for each day of the year (There are 365 rows that contain the year "2005"). However, I want to write a query that, given a date range and a user group ID, prod...

day of week
Greetings Could anyone kindly help me figure out how to add the day of the week to David Spelts' Traffic Log "Hits By Day"? I tried the following and could not implement any properly. I am a perl newbie and would appreciate any help. I imagine this is simple for a perl guru: #$dayofweek = Date_DayOfWeek($month,$day,$year); #my $date = ParseDateString("15 mar 1999"); $date = substr($key,0,6); print UnixDate($date, "%a"); #$dayofweek = UnixDate($date, "%A"); #my $dt = DateTime->new(year => $year, month => $month, day => $day); #my $d...

Day of the Week
In the real Appleworks, is there a way to determine the day of the week (Wed) from the date (Jan 4 04)? Robert Boucher <boucher1@alumni.texas.net> writes: > In the real Appleworks, is there a way to determine the day of the week > (Wed) from the date (Jan 4 04)? Not sure what you mean by "real Appleworks", but on the assumption that you mean in a spreadsheet, Zeller's congruence should do the trick: http://en.wikipedia.org/wiki/Zeller's_congruence In article <m2lk0sm7a7.fsf@RAWMBP.local>, Raymond Wiker <raw@RawMBP.local> wrote: > Robert B...

Day of Any Week
I have a COBOL method to get the Day of Any Week (after the year 1601), regardless of date input format, which can be any of the following, and includes spoken language as a parameter :- - Current-Date = ISO-8 (ISO-8 is of course Gregorian) - ISO-8 ccyymmdd - ISO-6 yymmdd - EU-8 ddmmccyy - EU-6 ddmmyy - NA-8 mmddccyy - NA-6 mmddyy Seems to work OK for 8-digit dates. I'm a bit dubious about 6-digit dates. Without going into specifics, take a 6-digit date for centuries 1600, 1700, 1800 - it might just fail - I wont know until I have tested the feature thoroughly. As I say it s...

DAY OF WEEK
David Frank posted on 30/8/2005 @ 0:21 >I posted yesterday in response to a request how to find certain weekdays in >a month, e.g. 3rd tuesday. >If PL/I has equivalent to Fortran integers and integer division then the >following syntax will be DIRECTLY translatable. Indeed it is directly translatable - in a coupla lines. ("when" contains day=1, month, year) w = days(when, 'DDMMYYYY'); dw = weekday (w); do j = 1 to 7; if j = dw then put (daystodate(w+(j-1+(ndw-1)*7))); end; If you like, we can print all the Mondays, or Tuesdays, etc., for the whole year. w = ...

days of a week
hi all, I have 2 variables, varYEAR and varWEEK (ie: 2010 and 50) I'd like to know the days of this week (it starts on mondays). Or at least the first day of this week. 2010-12-13 2010-12-14 2010-12-15 2010-12-16 2010-12-17 2010-12-18 2010-12-19 It seems the mysql date and time functions only take date variables and no week number variable. any hint ? regards, f. SELECT STR_TO_DATE('201050 Monday', '%X%V %W'); +-----------------------------------------+ | STR_TO_DATE('201050 Monday', '%X%V %W') | +-------------------------...

Day of the week
Hello, I'm looking for some code who return the day of the week from a date. For instance : May 18th 2003 --> saturday Any help is welcome. Titi Thierry wrote: > I'm looking for some code who return the day of the week from a date. > > For instance : May 18th 2003 --> saturday > http://www.php.net/date JW Janwillem Borleffs a �crit : > Thierry wrote: > >>I'm looking for some code who return the day of the week from a date. >> >>For instance : May 18th 2003 --> saturday >> > > > http://www.php.net/date Super...

Day of Week
I have an field called Date. I wish to have that translated into another field with the day of the week. Can anyone assist me in this? I assume you mean to translate a valid DATE (or Clarion LONG saved as a date) into the text equivalent Like "Monday", Tuesday". If this is the case, then there is many way to achieve this. The long way using EXECUTE: ----------------------------------- EXECUTE (YourDateVariable % 7) + 1 ! (Clarion date MOD 7) + 1 strDAY = 'Sunday' strDAY = 'Monday' strDAY = 'Tuesday' strDAY = 'Wednesday'...

day of week
Hi How can I calculate a three letter day of week (Mon, Tue, Wed etc.) from a date? Thanks Regards If your want to dispaly this in a textbox on a form or report, in the format property of the textbox type ddd. "John" <john@nospam.infovis.co.uk> wrote in message news:40d56746$0$1183$cc9e4d1f@news.dial.pipex.com... > Hi > > How can I calculate a three letter day of week (Mon, Tue, Wed etc.) from a > date? > > Thanks > > Regards > > Hi John, Try: Format([YourDate],"ddd") HTH, Don "John" <john@nospam.infovis.co.uk&g...

Day of the Week
I have an iPAQ 4150 with PPC 2003 1st ed. I live in the UK and would like to show the day of the week as well as the date in the Today screen. In Regional Settings this is only possible if I select the language as US. Is this possible with it set to UK? I could set RS to US and manually change the date settings etc but I can't then select pounds as the currency. "JoJo" <spam@nospam.com> wrote in message news:Kgc7d.82$C86.61@newsfe2-win.ntli.net... > I have an iPAQ 4150 with PPC 2003 1st ed. I live in the UK and would like to > show the day of the week ...

Day of Week
Gentle Listers: Pray tell, how does one determine the day-of-week it is? I vaguely recall some kind of Powerhouse or HP function like dayofweek = day(20040604) returning 5=Friday? Or was that in another incarnation? 8=) Many thanks for your input. Happy Friday in any event! John M Penney DDPS California Department of Corrections 1920 Alabama Ave. Rancho Cordova, CA (916)358-2247 (916)358-2322 Fax (360)789-8159 Cellular * To join/leave the list, search archives, change list settings, * * etc., please visit http://raven.utc.edu/archives/hp3000-l.html * ...

days of week
-> Making BASIC programs more portable is fun. How about this... Okay. Here's another little one that people may like to play with. It calculates the day of the week corresponding to any date on the Gregorian Calendar. So you can find out what day of the week you were born on, or the armistice that ended WW1 came into effect (November 11, 1918), and so on. This one is definitely in QBasic, with a FUNCTION. I guess this would have to be put into the mainline in many other BASICs. I made it a FUNCTION so it could be moved directly into other programs, if needed. ...

MYSQL : QUERY : Select two month where the day is lager than current day
Hi There, I'm having a bit of a problem with a MYSQL query, I think it=92s a simple one, only I can=92t figure it out! I'm trying to select dates bigger or equal to the current month, and bigger than the current day. Only the query is also excluding days in the next month. QUERY: SELECT DISTINCT koopzondag_month, koopzondag_day FROM koopzondag_dates WHERE (koopzondag_month >=3D '01' AND koopzondag_day >=3D '10') AND koopzondag_month <=3D '03' RESULT: 01-15, 02-15 I want the result to be RESULT: 01-15, 02-01, 02-15 TABLE: (I can=9...

gotta plug in again now -- its been lik 4 days on or a week on then 3 days off
i need to reconfigure looking around (there) gave me some informaiton anyway out imagine being some loser trying to address "metro" you fucking loser gravagno but losers control the game now what is metro? On Thursday, December 20, 2012 2:08:51 PM UTC-7, wjhonson wrote: > what is metro? This link might help: http://bit.ly/VUAeIm -- frosty ...

Query help? Return the date which is n business days before d (from a table with all days in it)
Hi all, I've got a table with columns: DATE (date type; contains all of the actual calendar days) IS_WORKDAY (0 or 1, depending on whether the DATE in that row is a work day) What I want: Suppose today is 1/20/2007. I want to know how many ACTUAL days back = 5 BUSINESS days back. For this example, the answer would be 6 (the 15th was apparently a holiday, according to our table). Another example: If today is a Monday, and I want the ACTUAL # of days corresponding to 1 BUSINESS day ago, three would be the answer (assuming Friday was a work day). What query will answ...