RE:Date format

[Chanty4u]

hi all,

how to chnage the date formats while loading...

if i hve the data with diffrnt date formats?

thanks in advance''''

Suresh

[tresesco]

Date(datefield, 'YourDesiredDateFormat') as NewDate

[prieper]

Do you mean that you will have different dateformats in the source-data?

If yes, you may use the ALT-funtion:

quote from OL:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

unquote

HTH Peter

[raajaswin]

Date(<DateColumn>,'Format') as NewDate

Format may be like MMDD, DDMM, DDMMM, DDMMMYYYY, DD/MM/YYYY ..etc...

[effinty2112]

Hi,

Try this if the the date you want to change is currently a text field rather than a Date field with a dual representation:

Date(Date#('YourDateText','OldDateFormat'),'NewDateFormat')

[Chanty4u]

chk attached sample excel

[jagan]

Hi,

Try like this using Date()

Syntax:

Date(DateFieldName, 'DateFormat')

Example:

Date(BillDate, 'DD-MMM-YYYY')

Date(BillDate, 'MM/DD/YYYY')

Date(BillDate, 'DD/MM/YYYY')  etc.

Hope this helps you.

Regards,

jagan.

[Chanty4u]

plz chk sample qvw

[tresesco]

Try like:

Date(

Alt(

     Date#(DateField, 'DD-MMM-YYYY') ,

          Date#(DateField, 'DD-YYYY-MMM'),

               Date#(DateField, 'MMM-DD-YYYY'),

                    Date#(DateField, 'MMM-YYYY-DD'),

                         Date#(DateField, 'YYYY-MMM-DD'),

                           Date#(DateField, 'YYYY-DD-MMM')

)

, 'YYYY-MMM-DD'

)

Edit: prieper‌ has already suggested similar. Corrected and included all posible date formats.

[raajaswin · Accepted solution]

Check this file.. Hope it helps