Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Converting Text to DateTime

I am receiving an excel file with date and time data on each record (e.g., Apr 21, 2013 10:15 PM).  I have tried to follow a number of the posts on this site on how to convert this to a datetime format within QlikView.  So far no luck.

Can someone please provide me sample code on how to make convert this from text to a single datetime field?

Thanks!

1 Solution

Accepted Solutions
MVP
MVP

Re: Converting Text to DateTime

to interpret the excel field

=date#('Apr 21, 2013 10:15 PM', 'MMM DD, YYYY hh:mm TT')

=date#(excelfield, 'MMM DD, YYYY hh:mm TT')

to interpret and format

=date(date#('Apr 21, 2013 10:15 PM', 'MMM DD, YYYY hh:mm TT'),'DD/MM/YYYY hh:mm:ss')

=date(date#(excelfield, 'MMM DD, YYYY hh:mm TT'),'DD/MM/YYYY hh:mm:ss')

8 Replies

Re: Converting Text to DateTime

Use Date and Date# function with Alt functions if required and if you provide any sample data then it will more easy to help.

ecolomer
Honored Contributor II

Re: Converting Text to DateTime

You can convert the format in the excel file to date (YYYY/MM/DD HH:MM) and use in QV the function Date and Date#

Not applicable

Re: Converting Text to DateTime

Thanks, Enrique.  I am getting these excel files automatically, and trying not to introduce any manual steps to ready it for QlikView.  Hoping to simply use code within QlikView to convert it to a datetime. 

Not applicable

Re: Converting Text to DateTime

Anand, attached is a sample Excel file.  I have tried both Date and Date# without success.

MVP
MVP

Re: Converting Text to DateTime

to interpret the excel field

=date#('Apr 21, 2013 10:15 PM', 'MMM DD, YYYY hh:mm TT')

=date#(excelfield, 'MMM DD, YYYY hh:mm TT')

to interpret and format

=date(date#('Apr 21, 2013 10:15 PM', 'MMM DD, YYYY hh:mm TT'),'DD/MM/YYYY hh:mm:ss')

=date(date#(excelfield, 'MMM DD, YYYY hh:mm TT'),'DD/MM/YYYY hh:mm:ss')

ecolomer
Honored Contributor II

Re: Converting Text to DateTime

OK,

if the format is allways the same you can process them:

Month=Month(left(date_time,3)

Day=Day((mid(date_time, 5,2)

Year=(mid(date_time,9,4) ....

Not applicable

Re: Converting Text to DateTime

Massimo, I can't tell you how many things I have tried but none seemed to work (obviously some typo).  This worked very well.  Thank you!

Not applicable

Re: Converting Text to DateTime

Got the answer.  Thanks!

Community Browser