Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

prashantsanchet
Contributor

Timestamp to date conversion

Hi Community,

I have a database column in the form of following. i want to get the date in (MM/DD/YYYY) how we write expression for same

Date_TS
20-Jan-18 05.12.12.000000000 AM
21-Jan-18 05.12.10.000000000 AM
9 Replies
YoussefBelloum
Esteemed Contributor

Re: Timestamp to date conversion

Hi,

you will need to use Date() and floor() functions.

try this on the script:

=Date(floor(your_field_name))

prashantsanchet
Contributor

Re: Timestamp to date conversion

This is not working

YoussefBelloum
Esteemed Contributor

Re: Timestamp to date conversion

did you replace the field name ?

can you show us what you have so far on the script and on a listbox via images ?

also, try this on the script:

=Date(Floor(Timestamp#(your_field,'DD-MM-YY hh. mm. ss TT'))) as date

and you use this date field on a listbox

Re: Timestamp to date conversion

May be something like this

=Date(Floor(Date#('20-Jan-18 05.12.12.000000000 AM', 'DD-MMM-YY hh.mm.ss.fff TT')))

Replace the hard-coded date with your field name in the above example

ckarras22
New Contributor III

Re: Timestamp to date conversion

Hi

date(left('20-Jan-18 05.12.12.000000000 AM',9),'MM/DD/YYYY')

sasiparupudi1
Honored Contributor III

Re: Timestamp to date conversion

=Date(Floor(Timestamp#(Date_TS,'DD-MMM-YY hh.mm.ss.fff TT')),'MM/DD/YYYY')

sumanta12
Contributor II

Re: Timestamp to date conversion

Hi Prashant,

You can try like this:

DATE(Date#(LEFT(Date_TS,9),'DD-MMM-YY'),'MM/DD/YYYY')

sumitkulkarni
New Contributor III

Re: Timestamp to date conversion

Hi Prashant,

If you have auto generated section in data load editor with autoCalender script then autoCalendar.Date may help you as it converts your date column Timestamp to various formats including  MM/DD/YYYY format.

DateColumn.autoCalendar.Date

If you don't have autocalender then below expression will work for sure.

=DATE(Date#(LEFT(Date_TS,9),'DD-MMM-YY'),'MM/DD/YYYY')

Thank you!

Best Regards,

Sumit Kulkarni

arunaerra
Contributor

Re: Timestamp to date conversion

Hi,

try below code,i have given tow expressions one only date and one for time stamp

1.Timestamp(Timestamp#(Date_TS,'DD-MMM-YY HH.MM.SS.[FFFFFFFFF] TT'),'MM/DD/YYYY hh:mm:ss tt')  AS DATE,



2.Date(Timestamp#(Date_TS,'DD-MMM-YY HH.MM.SS.[FFFFFFFFF] TT'),'MM/DD/YYYY') AS DATE2


I got o/p like below


date.PNG

Community Browser