Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
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
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

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