Day of Week in Query ?

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
gjchap99 (6)
1/26/2004 5:39:31 PM
comp.sys.ibm.as400.misc 9118 articles. 0 followers. Post Follow

3 Replies
846 Views

Similar Articles

[PageSpeed] 7
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
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
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
peter8622 (231)
1/27/2004 6:08:25 PM
Reply:
Similar Artilces:

finding which day has max sales
hi, i have data set like this; day sales monday 200000 tuesday 50000 sunday 90000 wednesday 150000; my question is how can i find which day has maximum sales.plz anyone can help ...

CapeSoft Tip of the Week: Tips for Multi-Proj: Multiple Backends and a Batch Compiler Tip
This is a multi-part message in MIME format. ------=_NextPart_000_004B_01C37EB3.079F6C00 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Tips for Multi-Proj: Adapting your application for different backends = and a Batch Compiler Tip=20 1) Using Multi-Proj to Adapt your Applications for Multiple Backends Lesley Dean recently posted an informative article on the newsgroup, = which has been used here to make this tip. This tip will be of interest to you if you are thinking of = implementing your application in multiple backends. For...

Windows 8 will be available on Oct 26 (5 days from today)
"That�s right! Just a few minutes ago, Steven Sinofsky announced at Microsoft�s annual sales meeting that customers will be able to get Windows 8 � whether in upgrade fashion or on a new PC � starting on October 26th." http://windowsteamblog.com/windows/b/bloggingwindows/archive/2012/07/18/windows-8-will-be-available-on.aspx ...

Convert day of year to month, day
Hi, What's the "ruby way" to go from a date/time format that is "Year Day_of_Year time" to "Year Month Day". E.g. I want to convert year=2008, day_of_year=095, frequently represented as "2008-095" to it's more usual format "2008-04-04". I've been playing with the Time class and it's methods and found nifty ways to get yday, e.g. Time.now.utc.yday, but how do I easily go backwards w/o writing my own simple converter? R -- Posted via http://www.ruby-forum.com/. On Apr 4, 2008, at 9:38 AM, Rob Redmon wrote: > What's ...

z88dk 32 column mode query
Inspired by Fuzzbuckets entry into the crap games comp I decided to get cracking on a few of my own. I think I might have already lost by attempting to write the game in C. But a crap game is even craper when you can't read the source so easily ;-) I am currently struggling to set the screen mode to print in a normal spectrum way but am stuck in 64 column mode. It's not speccy enougth and looks far to none crap in a crap kind of way. Does anyone know how to set the mode in a z88dk spectrum program? cheers sparkes ----== Posted via Newsfeeds.Com - Unlimited-Uncensored-Secu...

Calculate days
I need to do a program that calculate the days from a date whatever since 20XX to January 1st. 2000. How can I do that?? I have this, is it correct??? # include <iostream.h> # include <conio.h> void main() { int month, day, year, c_year, c_month, t_days; cout<<"Give a date dd/mm/yy: "<<endl; cout<<"Day: "; cin>>day; cout<<"Month: "; cin>>month; cout<<"Year: "; cin>>year; clrscr(); c_year=year*365; c_month=(month-1)*30; t_days=c_year+c_month+day; cout<<"Days from"<<day&...

Getting English Day Name
Hi, I am currently using DAYOFWEEK function and putting CASE statement to get english day name. For example: SELECT CASE DAYOFWEEK(CURRENT DATE) WHEN 1 THEN 'SUNDAY' WHEN 2 THEN 'MONDAY' WHEN 3 THEN 'TUEDAY' WHEN 4 THEN 'WEDNESDAY' WHEN 5 THEN 'THURSDAY' WHEN 6 THEN 'FRIDAY' WHEN 7 THEN 'SATURDAY' END FROM SYSIBM.SYSDUMMY1 The function DAYNAME is not working (or I am not using it rightly). Is there any other function that can be used to get the english day name as string? If DAYNAME can be used, w...

Has a day really gone by?
Without a posting here? Is this group dying? Or is it my ISP? -- Tony S. Anthony Sequeira <tony@sequeira.com> wrote: > Without a posting here? I saw some posts and posted something myself (then deleted it because I found the solution myself). So I guess it's your ISP or something else. Davide ...

Postal Lottery: Turn $6 into $60,000 in 90 days, GUARANTEED #3
Postal Lottery: Turn $6 into $60,000 in 90 days, GUARANTEED I found this in a news group and decided to try it. A little while back, I was browsing through newsgroups, just like you are now and came across a message just like this that said you could make thousands of dollars within weeks with only an initial investment of $6.00!!! So I thought yeah right, this must be a scam!!! But like most of us, I was curious, so I kept reading. Anyway, it said that you send $1.00 to each of the 6 names and addresses stated in the message. You then place your own name and address at the bottom of the li...

[News] Songbird Does Well on GNU/Linux, New FLOSS Weekly Audiocast
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Singing The Praises Of Songbird 1.0 ,----[ Quote ] | THERE'S no shortage of quality music player/jukebox software available on the | internet and in the past I've used iTunes, Amarok, Rhythmbox, Exaile, | Audacious and several others. `---- http://reddevil62-techhead.blogspot.com/2009/02/singing-praises-of-songbird-10.html FLOSS Weekly 55: jQuery http://twit.tv/floss55 Recent: FLOSS 49: XMPP ,----[ Quote ] | Hosts: Randal Schwartz and Leo Laporte | | Guest: Peter Saint-Andre on Jabber/XMPP. | | Here's what's coming up f...

Stats comp.dcom.telecom.tech (last 7 days) #2
Stats comp.dcom.telecom.tech (last 7 days) Top 10 posters for the period: rank posts kbytes name <address> 1 1 4.1 Richy <no@spam.ming> 2 1 3.6 Dr Balwinder S Dheeman <bsd.SANSPAM@cto.homelinux.net> 3 1 2.9 Rich Piehl <rpiehl5REMOVETHIS@FORNOSPAMcharter.net> 4 1 2.8 nonford <sales@pcm30.com> 5 1 2.7 Charles P. <charles@NOSPAMpattcom.com> 6 1 2.5 PC Telecom <sales_at_pctelecom_dot_com@foo.com> 7 1 2.0 Steves <ssolton@comcast.net> 8 1 2.0 Holmes...

dumb Qs of the week
I',m trying to align some eyes, layer over layer, but on the distortion tool, which I've always thot of as the correct one, has 6 or 8 adjustments, including pivot, which is what I need, but it doesn't, not like want. I need a pivot on center, to rotate one eye around and down, in an arc, but no tool adjust does that, help? Greg http://gregsplace.50megs.com http://www.picturetrail.com/fugitive1 gregfarr wrote: > I',m trying to align some eyes, layer over layer, but on the > distortion tool, which I've always thot of as the correct one, has 6 > or 8 adjustments, ...

SQL query #2 362524
So, this SQLquery went it a bar. After looking around, approached 2 tables and asked, "May I join you?...." onedbguru wrote,on my timestamp of 25/11/2010 11:27 AM: > So, this SQLquery went it a bar. After looking around, approached 2 > tables and asked, "May I join you?...." > <groan> :) On Nov 25, 1:27=A0am, onedbguru <onedbg...@yahoo.com> wrote: > So, this SQLquery went it a bar. =A0After looking around, approached 2 > tables and asked, "May I join you?...." "But of course, we have no constraints..." :-...

VMS opportunities HP misses every day
http://zdnet.com.com/2100-1105_2-5202236.html Who needs this sh*t day after day from Microsoft?? Linux weenies and their appliances are ok, but it isn't VMS. A small Alpha with OpenVMS, MX, Apache/Tomcat, Samba, MySQL, (PDMF, or the now abandoned HP mail thingy), a couple of pretty GUI tools to admin the thing, all configured and sold as a turnkey "appliance", sort of like the Sun/Cobalt stuff was. Sold in 1 CPU, dual CPU, or multiple CPU configurations, with/without RAID. Starting at <$1000 USD or thereabouts, complete with PDF versions of out-of-print VMS books that the...

[News] Richard Stallman Gives Public Talks, Inspires, and Launches DRM Awareness Day
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 'Free software' proponent to speak at UB ,----[ Quote ] | Well-known computer programmer Richard | Stallman will visit the University at | Buffalo Monday evening to discuss copyright | laws in the age of the Internet. `---- http://buffalo.bizjournals.com/buffalo/stories/2010/02/22/daily7.html Richard Stallman, President of Free Software Foundation Presents: Copyright vs. Community in the Age of Computer Networks ,----[ ] | Who: Richard Stallman launched the | development of the GNU operating systems in | 1984. GNU is free softwar...

How to make $100,000 in 20-60 days
$1.00 to each of the 6 names and addresses stated in the article. You then = place your own name and address in the bottom of the list at #6, and post t= he article in at least 200 newsgroups. No catch, that was it. So after thin= king it over, and talking to a few people first, I thought about trying it.= I figured: "what have I got to lose except 6 stamps and $6.00, right?" The= n I invested the measly $6.00. Well GUESS WHAT!?... within 7 days, I starte= d getting money in the mail! I was shocked! I figured it would end soon, bu= t the money just kept coming in. In my first...

Changing a duplicate select query into a delete query, help!!
Hello, I am trying to change a select, find duplicates, query into a delete query. I want to get rid of the records in the main table "tblHurnsHistory." I changed the Find Duplicates query to a Delete query. I then received an input box looking for criteria that states," Enter parameter value qryHurnsHistory.Grads_creditsEarned Then it deletes 0 records. I need it to delete the 48 that the Find Duplicates query returned. I am a little rusty at SQL, can you help me so the following SQL deletes the records returned by the FindDuplicates query. DELETE HHDelete.SyStudentID...

Zetia 30 day trial
Side effects of Zetia 10 mg Zetia mg Zetia 10mg tablets Zetia discount card 180 Zetia 'Zetia 5 Mg' (http://41zetia5.multiply.com) Pill Zetia Generic for Zetia 10 mg Low cost Zetia How much does Zetia cost 'Multiply' (http://29costzetiadrug.multiply.com) Much does Zetia cost without insurance Pill Zetia used Where to buy Zetia online Zetia 10 mg image 'Buy Zetia 10 Mg' (http://47buyzetia10.multiply.com) Cheapest place to buy Zetia Order Zetia 10 Zetia Zetia 10 mg para que serve 'Lowest Price For Zetia' (http://82lowestpricezetia.multiply...

Data Source URL in XML Query in reports
How can I make the URL for the Data Source of an XML query in Reports dynamic? I'm using Reports Developer 9i. I have a stored procedure that selects the xml from a clob in the database, and I want to pass a parameter to the report with the primary key of the row that I want the report to be based on. I would like the URL to be something like: http://servername/package_name.procedure?p_record_id=XXXX I tried creating a user parameter and putting in this: http://servername/package_name.procedure?p_record_id=:P_RECORD_ID but this did not work. Any ideas? Thanks, J...

From God The Father
From God The Father - A Letter For All Those Who Have Ears to Hear The Day of Slaughter, The Day of Glory, A Day of Harsh Judgment, A Day of Deliverance, A Day of Recompense,The Day of Astonishment... The Great and Terrible Day of The Lord! [God The Father] It will surely happen, it shall surely come to pass, says The Lord... They shall glorify My name in the Day of Slaughter. They shall sing in the face of adversity, when troubles encompass them round about, says The Lord, Whose judgment stands and shall fill the earth in His fiery anger. Behold! The Lord, The Lord of Hosts, shall show the w...

Re: Finding out of weekly sum
Chait , You also ddint mention whether you wanted the weeks to start on sunday and go 7 days, whether you wanted the seven day count to start on the 1st and a week is defined as six days from that date, or if you wanted to use the calander. Also, you never mentioned what the Total was made ofm a cumulative sum by week or the whole data set. And finally you didnt mention ehat version of SAS you have which makes a difference in what one can use in the way of functions and how much code it will take. So here is a solution which I think can be refactor to run faster and assumes version 9 and ...

looping through results of a OSQL query
Hello, I have the following problem. I wrote a batch file that runs a sql script on SQLServer 2000. This script must be executed on several databases. This works fine. The problem is that in my database is a table that holds a databases i have to update with this script. What i want is run a query with osql in my batchfile to retrieve this these records so I can loop through them an run the script for those databases. I managed to execute the query that return the records and write them to a textfile. What i want is store the results in some kind of a resultset so i can loop through them ...

Stats comp.mail.sendmail (last 7 days) #43
"Caveat: Quantity is not necessarily a measure of Quality" Newsgroup.................: comp.mail.sendmail Stats Were Taken..........: Mon, 13 Dec 2004 08:54:40 GMT Stats Begin...............: Mon, 06 Dec 2004 13:27:50 GMT Stats End.................: Mon, 13 Dec 2004 04:41:33 GMT Days......................: 7 Total No. of Articles.....: 160 Total No. of Characters...: 303931 Total Volume..............: 296 Messages Per Day..........: 22.9 Characters Per Day........: 43418.7 Average Daily Volume......: 42 kB Total Posters This Week...: 78 Messages with Sigs........: ...

Are you home much this week?
I need some help with some graphics. You have photoshop of some equivalent? I either never knew how to do what I want or have forgotten. Dave ...

OT: Happy PI Day
In case you aren't sure how to celebrate this special day here are some suggestions! http://wiki.ehow.com/Celebrate-Pi-Day -- Have a great day, Ray ray.sparks@gmail.com * To join/leave the list, search archives, change list settings, * * etc., please visit http://raven.utc.edu/archives/hp3000-l.html * Ray Sparks wrote: In case you aren't sure how to celebrate this special day here are some suggestions! http://wiki.ehow.com/Celebrate-Pi-Day According to the page it's celebrated on 3/14 at 1:59 PM, but surely it should be celebrated at 1:59 AM. In...