Time and Date formatting

[joshrussin]

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.

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

[jwjackso]

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]

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]

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

[joshrussin]

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.