topic text to date in QlikView

text to date

Mon, 21 Nov 2011 06:53:42 GMT

hello,

is it possible to change from text to date format?

i have tried somthing like this but without success:

let MyDate = Date('21-Nov-2011');

any suggestion ?

thank you all .

avi

text to date

Mon, 21 Nov 2011 07:50:18 GMT

Hi,

take a search into QlikView Help guide for date keyword; maybe MakeDate function is the one that you are looking for.

regards

text to date

ali_hijazi — Mon, 21 Nov 2011 08:00:34 GMT

as I see your original date format is day-month-year

you can use the MakeDate function as follows:

MakeDate(right(yourDateString,4), mid(yourDateString,4,3), left(yourDateString,2))

hope this solves your problem

text to date

Anonymous — Mon, 21 Nov 2011 08:36:33 GMT

hi,

set vstartdate=21-nov-2011

set Date=date(date#(vstartdate,'MMM/DD/YYYY'),'DD/MM/YYYY');

text to date

Mon, 21 Nov 2011 09:03:59 GMT

thank you all for the fast answer,

it seems that the sulotion is not working,

i get null instead of date.

avi

text to date

Mon, 21 Nov 2011 11:52:25 GMT

Not sure what you are trying to do here. Something like below should return you 21-Nov-2011 as you wanted.

let vTest=date(makedate(2011,11,21),'DD-MMM-YYYY');

Re: text to date

Thu, 03 Oct 2013 12:48:28 GMT

Here is a solution that I believe will work for you.

First create an inline table called MonthName:

MonthName:

MAPPING LOAD * INLINE [

MonthAbbr, MonthNum

Jan, 1

Feb, 2

Mar, 3

Apr, 4

May, 5

Jun, 6

Jul, 7

Aug, 8

Sep, 9

Oct, 10

Nov, 11

Dec, 12

];

After that is in place, use the following script:

Date AS OrgDate,

MAKEDATE(Right(Date,4) , ApplyMap('MonthName', TextBetween(Date,'-','-')) , LEFT(Date,2)) AS Date,

You can also use the same script to retrieve year, month, quarter by using those functions before the MAKEDATE code.

Hope this helps!

Re: text to date

CELAMBARASAN — Thu, 03 Oct 2013 13:08:16 GMT

Try this

let MyDate = Date(Date#('21-Nov-2011', 'DD-MMM-YYYY'));