Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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,
Maybe something like this:
date(daystart(timestamp#("Your Field",'Your Input Timestamp Format')),'Your Desired Date Format') as EntryDate
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
Thank you. That did the trick.