topic Re: Convert to date format in QlikView

Convert to date format

cclayford — Fri, 01 Feb 2013 08:51:19 GMT

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

Re: Convert to date format

Fri, 01 Feb 2013 08:57:28 GMT

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

Re: Convert to date format

er_mohit — Fri, 01 Feb 2013 09:07:36 GMT

hii

try this

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

Re: Convert to date format

Fri, 01 Feb 2013 10:08:56 GMT

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'))

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