Posted: Thu Mar 12, 2009 11:50 am Post subject: Playing with dates in DB2 query
Hi all,
My requirement is: for requested date as input (that date is in some table), I need to send an output (in yearly format) with 10 years of data before requested date.
E.g. if Input date is: 2008-11-30, my output should show data from 1999-01-01 till 2008-11-30. You would have noted here that calculated from-date has to be always the start date of the year. And calculated to-date has to be the requested date. So, data returned will be (in yearly format) for years 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, and 2007 full years and for 2008 only till 2008-11-30. On the whole data from 10 separate years should be returned.
similarly if input date is: 2008-12-25, my output should show data from 1999-01-01 till 2008-12-25.
similarly if input date is: 2008-12-31, my output should show data from 1999-01-01 till 2008-12-31.
similarly if input date is: 2009-01-31, my output should show data from 2000-01-01 till 2009-01-31.
similarly if input date is: 2009-02-28, my output should show data from 2000-01-01 till 2009-02-28.
Writing a query for this did not seem simple to me (atleast to me). so I browsed the manuals and this forums but could not get help. So devised my own query and its working. I am posting this query here for two reasons: a) People may help me write better query than this one OR b) Although better or not, atleast junta has some query that they can use in case needed.
thanks.
Just for simplicity I am taking the date in below query from some table
query:
Code:
SELECT custom_date
,CASE
WHEN SUBSTR(CHAR(custom_date),6,5) = '12-31'
THEN DATE(SUBSTR(CHAR(YEAR(DATE(custom_date) + 1 DAY -
10 YEARS)),1,4) CONCAT '-01-01')
ELSE DATE(SUBSTR(CHAR(YEAR(DATE(custom_date) + 1 DAY -
9 YEARS)),1,4) CONCAT '-01-01')
END
FROM Table
where custom_date > '1900-01-01'
Joined: 26 Nov 2002 Posts: 12375 Topics: 75 Location: San Jose
Posted: Thu Mar 12, 2009 12:41 pm Post subject:
seekaysk,
I think it can be simplified. The logic is quite simple. You don't even need the case statement. Get the beginning date of the year for the custom date and subtract 9 years from it.
To get the beginning date of year subtract day of the year and add 1 day. for ex today is march 12 , so the day of the year would be 71. If we subtract 71 from today you would get 2008-12-31. Now adding 1 day to it would make 2009-01-01. To get your range just subtract 9 years from that.
Code:
SELECT CUSTOM_DATE
,CUSTOM_DATE - DAYOFYEAR(CUSTOM_DATE) DAYS + 1 DAY - 9 YEARS
FROM TABLE
where custom_date > '1900-01-01'
;
Oh my god...Bravo Kolusu! and simultaneously foolish on my side...This is what happens when we perceive the requirements as so complex...Anyways, I am happy it serves a good thread with a simple solution when people like me search for this kind of query in future. _________________ Thanks.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
