|
View previous topic :: View next topic
|
| Author |
Message |
roopannamdhari
Warnings : 1
New User
Joined: 14 Sep 2006
Posts: 71
Location: Bangalore
|
|
|
|
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 |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| That is why DB2 has a date data format! |
|
| Back to top |
|
|
roopannamdhari
Warnings : 1
New User
Joined: 14 Sep 2006
Posts: 71
Location: Bangalore
|
|
|
|
Hi,
filed is not in date datatype it is smallint .year and mmdd is 2 different column.
Thanks |
|
| Back to top |
|
|
ashok_srivatsav
Currently Banned
New User
Joined: 29 Jun 2007
Posts: 15
Location: India, Bangalore
|
|
|
|
SELECT * FROM <TABLENAME>
WHERE DATE(Name of the DATE column) BETWEEN '2007-12-27' AND '2008-01-09' |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| 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 |
|
|
roopannamdhari
Warnings : 1
New User
Joined: 14 Sep 2006
Posts: 71
Location: Bangalore
|
|
|
|
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 |
|
|
|
|
