Number of day in a quarter? 2

[glenpark]

Hey everyone,

I've been working on this concept for a little while but still having trouble knocking out a solution. Based on a timestamp, is it possible to extract what number of day it is in a quarter? For instance, January through March of 2007 would be quarter 1 and hence February 2nd would be day 33 in that quarter.

I was thinking perhaps using the QUARTER function to find out the quarter, then some kind of CASE statement to calculate the number of days in that quarter...but I'm finding that I would need some way of determining how many days there are in each of those months. Any suggestions are appreciated!

Thanks,
Glen

 

[Mercury2]

Do you mean that July 2nd would be day 2 and September 21st would be day 83 in the 3rd quarter ?

 

[blom0344]

I am sure you could write a nasty SQL expression, but an alternative is to add a small lookup table that contains values 1-366 with the appropriate value you are after.

Then use the dayofyear function to match the datatable to the lookup table.

I know, it is a drag to create the lookup , but hey we are talking just 366 values

Ties Blom

 

[glenpark]

Yeah I thought of that originally but then I realized it may not work....I have to account for the years 1900 to 1984. I'm going to run into the whole leap year issue so some years will have a different number of days. *barf* lol Thanks for the suggestions guys

 

[MarcLodge]

Glen,

Here's a bit of SQL that should do the trick. Swap current_date for the date you want.

Ties, this isn't too nasty is it??

SELECT
CASE QUARTER(CURRENT_DATE)
WHEN 1 THEN DAYOFYEAR(CURRENT_DATE)
WHEN 2 THEN DAYOFYEAR(CURRENT_DATE) -
DAYOFYEAR(DATE(SUBSTR(CHAR(YEAR(CURRENT_DATE)),1,4)||'-03-31'))
WHEN 3 THEN DAYOFYEAR(CURRENT_DATE) -
DAYOFYEAR(DATE(SUBSTR(CHAR(YEAR(CURRENT_DATE)),1,4)||'-06-30'))
WHEN 4 THEN DAYOFYEAR(CURRENT_DATE) -
DAYOFYEAR(DATE(SUBSTR(CHAR(YEAR(CURRENT_DATE)),1,4)||'-09-30'))
END
FROM SYSIBM.SYSDUMMY1

 

[blom0344]

Marc,

Can't test it, but seems a nice approach. However, I would not be surprised if the DB2 CHAR function will mess this up.
(the habit of adding leading zeroes to the string)

But hey, my idea would need some tuning as well to cope with the leap years..

Ties Blom

 

[MarcLodge]

Ties,
I tested it on current date and it seemed to work ok, although I must admit I didn't try it on others. The CHAR did mess it up, hence the SUBSTR in there to get round that problem.

I went down the SQL path because at my site adding new tables is a bit of a pain, having to involve the DBAs and get it promoted etc. Your solution works well, I just couldn't resist the challenge in SQL

At least now Glen has two options to choose from.

Marc

 

[glenpark]

Thanks Marc, that statement worked perfectly! It's exactly what I needed. I'm basically writing a stored proc that'll fill in this insane date/time dimension table...it's done for the most part except for a couple problematic columns like this one.

I don't suppose you have a solution for figuring out which dates are the last dates in a quarter? I've been trying to cook up something but I run into some syntax problems:

select 
(to_date(concat(substr(to_char(current_date,'YYYY-MM-DD HH24:MI:SS'),1,4),'-03-02 00:00:00'),'YYYY-MM-DD HH24:MI:SS') - day(to_date(concat(substr(to_char(current_date,'YYYY-MM-DD HH24:MI:SS'),1,4),'-03-02 00:00:00'),'YYYY-MM-DD HH24:MI:SS') + 1 month - 1 day)) from 
SYSIBM.SYSDUMMY1;

Error: SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007

I get the distinct feeling I'm making this way harder than it should be lol Any help is appreciated!

 

[MarcLodge]

Glen,
What are you trying to do with the last date in a quarter? I'm obviously missing something here as the last date in a quarter never changes, it's always one of four. Please let me know what it is you're attempting to discover.

Marc
Thanks for the * !

 

[glenpark]

There's a column I'm filling in which is basically a flag to mark which dates are the last date in a quarter. Whenever a date comes up, I just mark the column 'Y' while all others are 'N'. Are you sure these particular dates never change? If so, then that makes is much easier.

 

[MarcLodge]

Glen,
Tell me I'm wrong, but I would have thought that the last day in each quarter is YYYY-03-31, YYYY-06-30, YYYY-09-30 and YYYY-12-31.

That's the rule that that bit of SQL that I passed works under, and if you are given a random date and want to know what the last date of the quarter is that that date is in, just use the case statement on it to give you the date.

If the date you are given is called to_date, then the SQL would be:
SELECT
CASE QUARTER(CURRENT_DATE)
WHEN 1 THEN DATE(SUBSTR(CHAR(YEAR(TO_DATE)),1,4)||'-03-31')
WHEN 2 THEN DATE(SUBSTR(CHAR(YEAR(TO_DATE)),1,4)||'-06-30')
WHEN 3 THEN DATE(SUBSTR(CHAR(YEAR(TO_DATE)),1,4)||'-09-30')
WHEN 4 THEN DATE(SUBSTR(CHAR(YEAR(TO_DATE)),1,4)||'-12-31')
END
FROM SYSIBM.SYSDUMMY1

Please note that I have not had the chance to test this at all.

Marc

 

[glenpark]

Yeah I'm not sure if those days change or not...I checked a calendar for 10 years worth of dates and it looks like it doesn't. So I created a case statement similar to yours only it just looks for the month and day for those specific end of quarters regardless of year:

case 
	when day(loaddate) = 31 and month(loaddate) = 3 then 'Y'
	when day(loaddate) = 30 and month(loaddate) = 6 then 'Y'
	when day(loaddate) = 30 and month(loaddate) = 9 then 'Y'
	when day(loaddate) = 31 and month(loaddate) = 12 then 'Y'
	else 'N'
end case

I haven't tested it yet but the logic seems sound so far.