Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Need inputs on Space requirement and... ashek15 IMS DB/DC 0 Sat Apr 01, 2017 8:26 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us