basic date loading syntax

[toddbuss]

Pardon my noob question.  I've looked through the helpfiles and forum and don't come up with a solution...

I'm trying to load dates formatted like this in a CSV file:

8/15/2018  10:22:00 AM

Here's the date function I'm using during the load:

date("DATE & TIME",'M/D/Y  hh:mm:ss tt') as ReportDate,

I have tried several variations of it, but all I load is nulls.  There's gotta be a simple syntax change to fix this.

Thanks,

Todd

[juraj_misina · Accepted solution]

Hi Todd.

what you actually need is Timestamp#() function which interprets string input (value coming from the source csv) as a timestamp. So a simple change:

Timestamp#("DATE & TIME",'M/D/Y  hh:mm:ss tt') as ReportDate,

should do the trick. If you only need the date part (not time info), you can adjust further with:

Date(Floor(Timestamp#("DATE & TIME",'M/D/Y  hh:mm:ss tt'))) as ReportDate,

Hope this helps.

Juraj

[Anil_Babu_Samineni]

Perhaps this? I feel that [DATE & TIME] is one field

Date(Date#([DATE & TIME],'M/DD/YYYY  hh:mm:ss TT'),'M/DD/YYYY') as ReportDate

[toddbuss]

thanks.  the date & floor functions didn't work as intended, but I have enough to punch through for now.  Timestamp# did the trick.