topic Re: Time and Date formatting in QlikView

Time and Date formatting

joshrussin — Fri, 30 Mar 2018 22:11:22 GMT

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?

Re: Time and Date formatting

jwjackso — Fri, 30 Mar 2018 22:44:54 GMT

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/DateAndTimeFunctions/date-time-functions.htm)

The fractional part should be seconds since midnight.

Re: Time and Date formatting

joshrussin — Sat, 31 Mar 2018 00:27:50 GMT

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.

Re: Time and Date formatting

vishsaggi — Sat, 31 Mar 2018 01:29:57 GMT

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

Re: Time and Date formatting

joshrussin — Sat, 31 Mar 2018 03:04:59 GMT

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.