Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
dmelillo
Contributor III

Convert 19-Jul-2018 1:34PM to Date

I have a date field, example,

19-Jul-2018 1:34PM

and I would like to convert to

07/19/2018

How would this be most easily accomplished in load script?

Tags (1)
1 Solution

Accepted Solutions
Highlighted
vamsee
Valued Contributor

Re: Convert 19-Jul-2018 1:34PM to Date

Hello,

Date(Floor(Timestamp#('19-Jul-2018 1:34PM', 'DD-MMM-YYYY hh:mmtt')))

if you need a preceding 0 in the month then change the variable edit script.

         

          SET DateFormat='MM/DD/YYYY';

                         or

          Date(Floor(Timestamp#('19-Jul-2018 1:34PM', 'DD-MMM-YYYY hh:mmtt')), 'MM/DD/YYYY')

Thanks.

7 Replies
Chanty4u
Esteemed Contributor III

Re: Convert 19-Jul-2018 1:34PM to Date

TRY THIS

date(Date#(Datefield,'DD-MMM-YYYY hh:mmTT'),'DD/MM/YYYY')  as newdate

uroboros
Contributor

Re: Convert 19-Jul-2018 1:34PM to Date

Hi Dave, use Date(Date#('19-Jul-2018 1:34PM','DD-MMM-YYYY hh:mmtt'),'DD/MM/YYYY')

It should work. Regards!

Chanty4u
Esteemed Contributor III

Re: Convert 19-Jul-2018 1:34PM to Date

My bad

date(Date#(Datefield,'DD-MMM-YYYY hh:mmTT'),'MM/DD/YYYY')  as newdate

OR

date(Date#(Datefield,'19-Jul-2018 1:34PM'),'07/19/2018')  as newdate

guitartrova
Contributor III

Re: Convert 19-Jul-2018 1:34PM to Date

Try

date#(date(Date_FIELD),'MM/DD/YYYY') as date

Highlighted
vamsee
Valued Contributor

Re: Convert 19-Jul-2018 1:34PM to Date

Hello,

Date(Floor(Timestamp#('19-Jul-2018 1:34PM', 'DD-MMM-YYYY hh:mmtt')))

if you need a preceding 0 in the month then change the variable edit script.

         

          SET DateFormat='MM/DD/YYYY';

                         or

          Date(Floor(Timestamp#('19-Jul-2018 1:34PM', 'DD-MMM-YYYY hh:mmtt')), 'MM/DD/YYYY')

Thanks.

dmelillo
Contributor III

Re: Convert 19-Jul-2018 1:34PM to Date

Thank you kind sir!!!!!!!

vishsaggi
Esteemed Contributor III

Re: Convert 19-Jul-2018 1:34PM to Date

May be another way is like:

Date(Date#(Trim(Subfield(Timestmp, ' ',1)), 'DD-MMM-YYYY'),'MM/DD/YYYY') AS TimeID