Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ 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
View previous topic :: :: View next topic  
Author Message
roopannamdhari
Warnings : 1

New User


Joined: 14 Sep 2006
Posts: 71
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

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
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

New User


Joined: 14 Sep 2006
Posts: 71
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: 1512
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

New User


Joined: 14 Sep 2006
Posts: 71
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
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am
No new posts DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us