Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

turning text into date in script

I have a couple tables where the dates in the older files come thru as text

2007/12/01 00:00:00

1/12/2007 00:00

I want to convert these to dates in the script, is this possible? Qlikview does not recognize them as dates even if I use teh date() function. I tried teh below but I get Error in expression ')' expected

if
(mid(Date,5) = '/' ,

left(Date,4) & '/' & mid(Date,6,2) & '/' & mid(Date,9,2,) as Date,

Date as Date),

Tags (1)
2 Replies
vgutkovsky
Honored Contributor II

turning text into date in script

You need to use the timestamp# transformation in combination with date(). So something like this:


date(timestamp#(datefield,'MM/DD/YYYY hh:mm')) as Date


Or:


if(left(datefield,5)='/',
date(timestamp#(datefield,'YYYY/MM/DD hh:mm')),
date(timestamp#(datefield,'MM/DD/YYYY hh:mm'))
) as Date


Regards,

MVP & Luminary
MVP & Luminary

turning text into date in script

Look up functions date#() and date() in the Help Section - the first function is casting string into a date, and the second one formats the date to a desired date format.

Generally speaking, QlikView Help Section is organized fairly logically - if you need "Date Functions", search for the string and you'll typically find what you need...