Convert to date format

[cclayford]

Hi all

Please can you show me how to convert the following date format so that i can have the "Year", and "Month" functions:

Mon Mar 01 22:19:49 +0000 2010

Many thanks,

Christopher

[#2]

hi

try this it work fine

date(date#('Mar 01 22:19:49 2010','MMM DD hh:mm:ss YYYY'),'M/D/YYYY h mm ss') as dd

[er_mohit]

hii

try this

date(floor(timestamp#(replace(DATEFIELD,' ',' '),'mmm dd yyy h:mm:tt')),'DD/MM/YYYY')AS NEW_DATE_FIELD

[#4 · Accepted solution]

Hi Christopher,

     Except of the week day, that is redundant, the rest of timestamp components can be converted with the Timestamp() function using the correct format code. Thus, I have used Mid() function to remove the first weekday characters, and then converted to a Timestamp() type:

=Timestamp#(Mid('lun Mar 01 22:19:49 +0000 2010', 5), 'MMM DD hh:mm:ss +ffff YYYY')

     If you want to work with this field to extract other date or time components, then try this:

Month(Timestamp#(Mid('lun Mar 01 22:19:49 +0000 2010', 5), 'MMM DD hh:mm:ss +ffff YYYY'))

or

Year(Timestamp#(Mid('lun Mar 01 22:19:49 +0000 2010', 5), 'MMM DD hh:mm:ss +ffff YYYY'))