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 Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SORT requirement sjiraga COBOL Programming 6 Thu Mar 09, 2017 9:11 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am


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