Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
joshrussin
Contributor III

Time and Date formatting

Hello all,

I have a database that records time and date as 'MM/DD/YYYY hh:mm:ss' after the seconds it displays am or pm. Ex. "3/29/2018  9:56:09 AM"

I went to add a filter box by date and it shows a bunch of different days that contain the same date. I assume it's becasue the time is an addition to the date. I tried to do the following to help

Date_FRL,

    alt(

    date#(Date_FRL,'MM/DD/YYYY hh:mm:ss')

        )

        as AltDate_FRL,

After this alter to the load script, the filter box shows this.

Capture-0095.jpg

Any ideas on how to get the date and time to be in a regular format?

4 Replies
jwjackso
Valued Contributor

Re: Time and Date formatting

Try putting a Date around your current Date#,

Date_FRL,

    alt(

    date(date#(Date_FRL,'MM/DD/YYYY hh:mm:ss TT),'MM/DD/YYYY')

        )

        as AltDate_FRL,


The number is number of days since Dec 30, 1899 (https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFun...)


The fractional part should be seconds since midnight.

joshrussin
Contributor III

Re: Time and Date formatting

I tried this,

Date_FRL,

    alt(

    date(date#(Date_FRL,'MM/DD/YYYY hh:mm:ss TT'), 'MM/DD/YYYY')

        )

        as AltDate_FRL,


Didn't seem to work. Now nothing shows in the filter.

vishsaggi
Esteemed Contributor III

Re: Time and Date formatting

Can i know why you are using Alt for your Datefield? Can you also share your excel file to look into please?

joshrussin
Contributor III

Re: Time and Date formatting

I was able to do this now. I had to change something in my database for the time and date to come through in a different output. Thanks for your help!

Hey Vishwarath, can you take a look at my other inquiry?

Select date ranges as a dimension in table.