Convert Text Field to Date

[jamiemcc]

Hi Guys,

I have attached two different formats (Created Column)  - One as text and other as date . Im trying to read into QLIK but I cant convert text field to date? I want to read both Columns as one into QLIK.

How do I combine and convert into date in QLIK?

Thanks,

Jamie

[RafaelBarrios]

HI @jamiemcc 

i cant see any date in you file, only a created time

in any case, you would have to use date#() and specify the format in which you are receiving the date.

https://help.qlik.com/en-US/sense/May2022/Subsystems/Hub/Content/Sense_Hub/Scripting/InterpretationF...

lets say your app format is MM/DD/YYYY and in your data you have 2022-11-1

you can do this:

date#(date_field,'YYYY-MM-DD')

 same thing for time (tt for am/pm)

time#(date_field,'hh:mm:ss tt')

and if you have date and time you can do this

2022-11-1 12:04:00 AM

date#(date_field,'YYYY-MM-DD hh:mm:ss tt')

hope this helps.

Best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂

[jamiemcc]

Thanks Rafael for your reply.

When I bring In the file to QLIK and try to treat it as a date Date(Created) it brings up null like attached.

Why isn't it treating it like a date? 

TryRating:
LOAD Distinct *,
FileName() as FileName,
FileTime() as FileTime,
date#(Created,'YYYY-MM-DD') as Created1,
date#(Created,'YYYY-MM-DD hh:mm:ss tt') as Created2,

[brunobertels]

hi 

try this 

date(date#(left('2022-10-31 05:54:22.503600+00:00',10),'yyyy-mm-dd'),'yyyy-mm-dd')

replace '2022-10-31 05:54:22.503600+00:00' with your dimension Created

[SunilChauhan]

Try this in script

MakeTime(Subfield(Created,':',1),subfield(Subfield(Created,':',2),'.',1),subfield(Subfield(Created,':',2),'.',2)) as CreatedNum

or

MakeTime(Subfield(Created,':',1),subfield(Subfield(Created,':',2),'.',1),subfield(Subfield(Created,':',2),'.',2))

on Gui

[RafaelBarrios]

hi @jamiemcc 

as i think you wont need the timezone +00:00

you could try  taking it out

subfield('2022-10-23 14:30:59:123456+00:00','+',1)

and then specify your timestamp format if Qlik doesnt do it automaticaly
from what i can see it should, but if not

timestamp#(subfield('2022-10-23 14:30:59:123456+00:00','+',1),'YYYY-MM-DD hh:mm:ss:fff')

for you would be

timestamp#(subfield(Created,'+',1),'YYYY-MM-DD hh:mm:ss:fff')

Hope this helps.

Best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 🙂