Portal | IBM Manuals | Downloads | Products | Refer | Info | Programs | JCLs | Forum Rules*| Site Map | Mainframe CD 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index
 
Register
 
IBMMAINFRAMES.com - IBM Mainframe Support Forums Index FAQ Search Memberlist Usergroups Profile Log in to check your private messages Log in
 
sql Query for the metioned requirement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
Author Message
roopannamdhari
Warnings : 1
Active User


Joined: 14 Sep 2006
Posts: 53
Location: Bangalore
PostPosted: Thu Aug 07, 2008 3:49 pm    Post subject: sql Query for the metioned requirement
 Reply with quote
Hi,

Can any one suggest me the sql query for the following requirement.
need to selct records from a table with a date between 20071227 to 20080109

table struacture
yyyy- smallint
mmdd- smallint

yyyy mmdd
2005 1201
2006 1228
2007 1227
2008 0101
2008 0108
2006 1208

Thanks,
Back to top
View user's profile Send private message
References
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 989
Location: Virginia, USA
PostPosted: Thu Aug 07, 2008 5:06 pm    Post subject:
Reply with quote
That is why DB2 has a date data format!
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1
Active User


Joined: 14 Sep 2006
Posts: 53
Location: Bangalore
PostPosted: Thu Aug 07, 2008 5:12 pm    Post subject: Reply to: sql Query for the metioned requirement
 Reply with quote
Hi,

filed is not in date datatype it is smallint .year and mmdd is 2 different column.

Thanks
Back to top
View user's profile Send private message
ashok_srivatsav
Currently Banned
New User


Joined: 29 Jun 2007
Posts: 15
Location: India, Bangalore
PostPosted: Thu Aug 07, 2008 5:18 pm    Post subject: Reply to: sql Query for the metioned requirement
 Reply with quote
SELECT * FROM <TABLENAME>
WHERE DATE(Name of the DATE column) BETWEEN '2007-12-27' AND '2008-01-09'
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 989
Location: Virginia, USA
PostPosted: Thu Aug 07, 2008 5:29 pm    Post subject: Re: sql Query for the metioned requirement
 Reply with quote
roopannamdhari wrote:
Hi,

Can any one suggest me the sql query for the following requirement.
need to selct records from a table with a date between 20071227 to 20080109

table struacture
yyyy- smallint
mmdd- smallint

yyyy mmdd
2005 1201
2006 1228
2007 1227
2008 0101
2008 0108
2006 1208

Thanks,


Code:
SELECT * FROM TABLE1
WHERE (YYYY = 2007 AND MMDD >= 1227)
       OR (YYYY = 2008 AND MMDD <= 0109)

 
Back to top
View user's profile Send private message
roopannamdhari
Warnings : 1
Active User


Joined: 14 Sep 2006
Posts: 53
Location: Bangalore
PostPosted: Fri Aug 08, 2008 10:57 am    Post subject:
Reply with quote
Hi Craq Giegerich,

Thanks it is working... for date range 20071227 to 20080109....

Concern to ease requirement understanding i specified 2 fixed date. sorry ...

Actually I have to retrieve records of 7 days.

Means need to retrieve records from 7 days prior to current date till current dates.
Example: current date:-20080809 (yyyymmdd)
7 days prior to current date: - 20080803 (yyyymmdd)

Here I have to retrieve records with date range 20080803 to 20080809

Quote:
SELECT * FROM TABLE1
WHERE (YYYY = 2007 AND MMDD >= 1227)
OR (YYYY = 2008 AND MMDD <= 0109)

It works fine for date range from 20071227 to 20080109 (my previous requirement)

But for date range 20080803 to 20080809, it will retrieve all the records with year = 2008 and mmdd < 0109. This will go out of range

Thanks
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1