topic turning text into date in script in QlikView

turning text into date in script

Mon, 30 Aug 2010 23:16:44 GMT

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),

turning text into date in script

vgutkovsky — Mon, 30 Aug 2010 23:23:07 GMT

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,

turning text into date in script

Oleg_Troyansky — Mon, 30 Aug 2010 23:27:13 GMT

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...