topic Having problem loading data as DATE format in QlikView

Having problem loading data as DATE format

Tue, 23 Mar 2010 20:36:53 GMT

I have tried:

1) Date([Begin Date]) as EntryDate and

2) Date(Date#([Begin Date], 'YYYY-MM-DD')) as EntryDate

but keep getting "null" or "-" as the result. Attached is the original excel data.

Many thanks.

Having problem loading data as DATE format

vgutkovsky — Tue, 23 Mar 2010 21:36:01 GMT

Looks like your excel data is formatted as a timestamp. If there is a way you can change the formatting, I would recommend that you do that. Otherwise, try using the timestamp# transformation and then use makedate with year(field), month(field), and day(field).

Regards,

Having problem loading data as DATE format

johnw — Wed, 24 Mar 2010 00:15:06 GMT

Maybe something like this:

date(daystart(timestamp#("Your Field",'Your Input Timestamp Format')),'Your Desired Date Format') as EntryDate

Having problem loading data as DATE format

Anonymous — Wed, 24 Mar 2010 03:51:22 GMT

Your problem here is the "GMT" in the timestamp value. When using Date# to interpret a value you need to capture and parse the entire string like this: date#(DATE, 'YYYY-MM-DD hh:mm GMT'). We can't however write it like this since M in the interpretation represents Month and there is unfortunately no kind of escape character. If the timestamps all look the same and you want to extract just the date you could just strip the first part of the timestamp values out and format this as a date:

date(date#(left([Begin Date],10),'YYYY-MM-DD'),'Your Choice of Format') as EntryDate

Having problem loading data as DATE format

Wed, 24 Mar 2010 16:41:00 GMT

Thank you. That did the trick.