Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
toddbuss
Creator
Creator

basic date loading syntax

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

1 Solution

Accepted Solutions
juraj_misina
Partner Ambassador Affiliate
Partner Ambassador Affiliate

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

View solution in original post

3 Replies
juraj_misina
Partner Ambassador Affiliate
Partner Ambassador Affiliate

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
toddbuss
Creator
Creator
Author

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