Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator 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?

1 Solution

Accepted Solutions
Highlighted
Specialist
Specialist

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.

View solution in original post

7 Replies
Highlighted
Champion III
Champion III

TRY THIS

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

Highlighted
Creator
Creator

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

It should work. Regards!

Highlighted
Champion III
Champion III

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

Highlighted
Creator III
Creator III

Try

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

Highlighted
Specialist
Specialist

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.

View solution in original post

Highlighted
Creator III
Creator III

Thank you kind sir!!!!!!!

Highlighted
Champion III
Champion III

May be another way is like:

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