Question about month

[jpockets]

I'm trying to extract month from the date. And i'm having trouble. My date is coming as following:
31-AUG-07

I been using Oconv(date, "D M[2]") but it's not working. I need to return 10, is Oconv the right way to do this?

Thank-you

[DSguru2B]

You need to first do an ICONV on it and then do an OCONV with just the month mask.

Code: Select all

OCONV(ICONV(in.Date, "D2-"), "DM[2]")

[jpockets]

Thanks for the reply. It's working but i'm getting an error message saying: Invalid character value for cast specification

LEDGER_DT = "31-AUG-07"
LEDGER_MONTH = 08
Ledger Month is defined as numeric 2
LEDGER_DT is defined as timestamp 23
this is the code i'm using to get ledger month:

[code]OCONV(ICONV(LP_POLICY_SALES.LedgerDate, "D2-"), "DM[2]") [/code]

Anybody know the reason driving this error?

[DSguru2B]

I see a problem with the model. '08' is not numeric, its varchar or char. 8 is numeric. If you want a numeric field, then you wont get '08'. If you, absolutely want a two digit month then you have to change the datatype of this field.
To prove my point, multiply the ICONV/OCONV code with 1 in the derivation and then insert, you will see what I am trying to explain.

[jpockets]

Thanks for the reply the problem was with the column ledger date so i used to this code and it worked:
[code]Oconv(Iconv(LP_POLICY_SALES.LedgerDate,"D2-"),"D-YMD[4,2,2]"): " 00:00:00"[/code]

Thanks for steering me in the right direction!

[ray.wurlod]

Time to mark the thread as Resolved, then?