How to convert ’Quarter’ with data type CHAR to data type DATE

[alex_]

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.   

[MFGF]

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.

[alex_]

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?

[MFGF]

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.