Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

MalcolmCICWF
Not applicable

Convert a string to Date & Time

I was wondering, what would be the easiest way to convert this string into a date and time with the AM/PM on it? I don't know who though it was a good idea to output an '@' in the middle of this. Right now I am doing a 'left' and taking out the date part and converting, but I am having issues removing the time and formatting. There must be an easier way...

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: Convert a string to Date & Time

Just use the timestamp interpretation function Timestamp#() with an appropriate format code:

=Timestamp(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT'))

edit: If you want to separate date and time, just use the timestamp returned:

=Date(Floor(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

=Time(Frac(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

2 Replies
swuehl
Not applicable

Re: Convert a string to Date & Time

Just use the timestamp interpretation function Timestamp#() with an appropriate format code:

=Timestamp(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT'))

edit: If you want to separate date and time, just use the timestamp returned:

=Date(Floor(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

=Time(Frac(Timestamp#('2013-07-30 @ 12:44 PM','YYYY-MM-DD @ hh:mm TT')))

tamilarasu
Not applicable

Re: Convert a string to Date & Time

Another solution could be,

=Date(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm TT))

=Time(Date#(Replace('2013-07-30 @ 12:44 PM',' @ ',' '),'YYYYY-MM-DD hh:mm tt'),'hh:mm TT')

You can also use PurgeChar syntax to remove the @ symbol form your string field.