Convert to Date

[boppanakrishna]

Hi,

How to Convert YYYYMM to Start date for Month
EX: 201011 to 01/11/2010

Regards,
BRK

[ray.wurlod]

Provide the correct format strings and use conversion functions such as DateToString() and StringToDate(). You will also need to concatenate the day number (01) to the incoming string.

[Ravi.K]

Derivation:

'01/':DateToString(StringToDate('201011',"%yyyy%mm"),"%mm/%yyyy")

[ray.wurlod]

More work may be required if the output has Date data type.

[Abhi700]

write a derivation


'01/':DateToString(StringToDate('201011',"%yyyy%mm"),"%mm/%yyyy")

[Ravi.K]

Yes,If output Datatype is "Date" then it requires additional formatting based on the Database Date format or Date format.

Suppose Date format is "dd-mmm-yyyy" then derivation is as follows.

StringToDate("01/":DateToString(StringToDate('201011',"%yyyy%mm"),"%mm/%yyyy"),"%dd-%mmm-%yyyy")

[ray.wurlod]

Please think this through a bit more carefully.

StringToDate("01/":DateToString(StringToDate('201011',"%yyyy%mm"),"%mm/%yyyy"),"%dd-%mmm-%yyyy")

The inner dates use "/" delimiter, but you specified "-" in the outer StringToDate() function. This won't work!

[Ravi.K]

oops. There is a small mistake. Thanks for the correction.

Revised Logic.

StringToDate(Convert('/','-',"01/":DateToString(StringToDate('201011',"%yyyy%mm"),"%mmm/%yyyy")),"%dd-%mmm-%yyyy")

[ray.wurlod]

Now re-read the original requirement, which was for "/" delimiters!

[Ravi.K]

Ok, Now goes to Original requirement 201011 should be marked as 01/11/2010 then if target datatype is Date.

Derivation:

StringToDate("01/":DateToString(StringToDate('201011',"%yyyy%mm"),"%mm/%yyyy"),"%dd/%mm/%yyyy")

Thanks