Issue with different date formats

[ferha_jafri]

Hello Everyone,

I am facing an issue as i have a table in which all the date fields are in different - different formats some are in number,some are in text some contains the date along with time stamp,due to which i am not able to get the correct value and correct selections,Please let me know what is best in this case.

Thanks in advance

With regards

Ferha Jafri

[#2]

Ferha,

Just apply

Date(date_field) as Date

in your script and you will be good to go.

[tresesco]

During load all of them should be transformed to proper date values using different date functions like:

Date(DateField, 'MM-DD-YYYY') as Date1

If you have text like 11Sep2013, then

Date(Date#(DateField, 'DDMMMYYYY'), 'MM-DD-YYYY') as Date2

Or, you might have to use MakeDate() function some where depending on your data format.

Otherwise, maintaining a master calendar is a good idea, though that depends on your model.

[#4]

Hello,

If you want to display your data as date, you can make Date(Date) in your script,

you can also make Date()*1 to have all in Number Format (it is sometimes helpfull to store data as number to make calculations and set analysis).

[Gysbert_Wassenaar]

See this blog post: Get the Dates Right