Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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),
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,
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...