Hi,
I have the following problem.
My Database is DB2, but I haven't access to it.
Now I'm working in Framework Manager Level.
I have column 'Quarter' which format is like '2011-Q1' with data type CHAR.
I am trying to convert 'Quarter 'to data type DATE.
So I can use function '_add_months ( '.
I use the follow functions to convert CHAR to DATE.
date ([Year Quarter])
OR
cast ([Year Quarter], date)
But both approaches receive this result: 'Nov 30, 0002'. This is obviously wrong.
How to convert properly CHAR to DATE, to receive '2011-Q1' with data type DATE?
Thanks.
The function _add_months doesn't do what you imagine. It simply adds a number of months to an already-complete date value
Example: _add_months ( 2012-04-30 , 1 )
Result: 2012-05-30
You need to extract the year from the expression, then use a CASE or IF-THEN-ELSE to convert Q1 to 01, Q2 to 04, Q3 to 07 and Q4 to 10 for the month, then add -01 to the end for the day. You will end up with a string that looks like (eg) 2011-01-01 for your original 2011-Q1 value. Cast the resultant string as a date using the cast() function.
Regards,
MF.
Thanks.
But... I'm not certain that understand completely.
Please, would you be a little more detailed.
What should I do to receive the previous Quarter?
Hi,
You didn't mention anything about receiving a previous quarter? You asked how to convert your current quarter string into a date.
An example of an expression to do this is here:
cast(substring ([Year Quarter], 1, 4) + case substring([Year Quarter], 6, 2) when 'Q1' then '-01-01' when 'Q2' then '-04-01' when 'Q3' then '-07-01' when 'Q4' then '-10-01' else '-02-01' end, date)
You could then use the _add_months() function around this to derive the previous quarter if you need to?
Do you want something different from this?
MF.