Having problem loading data as DATE format

[#1]

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.

[vgutkovsky]

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,

[johnw]

Maybe something like this:

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

[Anonymous · Accepted solution]

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

[#5]

Thank you. That did the trick.