topic Re: Converting string to date in QlikView

Converting string to date

Wed, 25 Nov 2020 16:16:04 GMT

hi,

i am loading data from SQL and the date format is in varchar and is looking like this 16AUG2016 , i am needing to convert it to date but i am batlling,

16AUG2016

26MAR2015

28AUG2015

03MAY2017

26MAR2015

09MAR2017

26MAR2015

13JAN2017

26MAR2015

can anybody assist please

regards,

marvin

Re: Converting string to date

prma7799 — Fri, 11 Aug 2017 10:59:45 GMT

Try this

=Date(Date#('16AUG2016','DDMMMYYYY'),'DD-MMM-YYYY')

Re: Converting string to date

antoniotiman — Fri, 11 Aug 2017 11:00:01 GMT

Hi Marvin

try this

Date(Date#(FieldDate,'DDMMMYYYY'))

Regards,

Antonio

Re: Converting string to date

Chanty4u — Fri, 11 Aug 2017 11:04:16 GMT

try this script

load * Inline [

Mydate

16AUG2016

26MAR2015

28AUG2015

03MAY2017

26MAR2015

09MAR2017

26MAR2015

13JAN2017

26MAR2015

];

result::

load

date(Date#(Mydate,'DDMMMYYYY'),'DD-MM-YYYY') AS Newdate

Resident A;

Re: Converting string to date

Fri, 11 Aug 2017 11:06:23 GMT

hi,

thanks for response , when i run script in the load statement it gives

20APR1970:00:00:00

20APR1979:00:00:00

20APR1980:00:00:00

Re: Converting string to date

Fri, 11 Aug 2017 11:14:23 GMT

hi,

thanks i loaded i still get the same result

20APR1970:00:00:00

Re: Converting string to date

Chanty4u — Fri, 11 Aug 2017 11:15:37 GMT

try same change below

load

date(Date#(Mydate,'DDMMMYYYY hh:mm:ss'),'DD-MM-YYYY') AS Newdate

Resident A;

Re: Converting string to date

prma7799 — Fri, 11 Aug 2017 11:35:22 GMT

Can you please share some sample data?

Re: Converting string to date

Fri, 11 Aug 2017 11:38:43 GMT

PIS_DATE

16AUG2016:09:55:01

26MAR2015:10:56:34

28AUG2015:12:37:40

03MAY2017:13:03:52

26MAR2015:09:28:32

26MAR2015:09:31:16

26MAR2015:09:32:06

09MAR2017:16:16:54

26MAR2015:09:33:33

13JAN2017:14:03:30

26MAR2015:09:40:45

26MAR2015:09:44:51

16APR2017:21:16:43

16JAN2017:08:29:20

26MAR2015:09:57:55

29JUL2015:00:31:05

26MAR2015:09:59:55

26APR2017:22:57:52

29JAN2016:07:25:20

20MAR2017:22:59:19

i have been try in sql to convert it but it seems this a in varchar

Re: Converting string to date

Chanty4u — Fri, 11 Aug 2017 11:39:53 GMT

Did you tried this?

Re: Converting string to date

madhuqliklondon — Fri, 11 Aug 2017 12:05:46 GMT

Hi,

may be this

LOAD

Date(Date#(PIS_DATE,'DDMMMYYYY:hh:mm:ss'),'DD-MM-YYYY') As PIS_DATE

FROM

..............

Re: Converting string to date

prma7799 — Fri, 11 Aug 2017 12:08:06 GMT

PFA....

Re: Converting string to date

prma7799 — Fri, 11 Aug 2017 12:36:54 GMT

Did you get right answer?

Re: Converting string to date

Fri, 11 Aug 2017 12:39:41 GMT

hi,

Thanks perfect it worked, now just to create the mast calendar for this model.

Re: Converting string to date

prma7799 — Fri, 11 Aug 2017 12:48:17 GMT

I think you marked wrong reply for correct answer ...

Re: Converting string to date

Fri, 11 Aug 2017 12:57:35 GMT

sorted ... sorry