Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
joshrussin
Creator III
Creator 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
Specialist II
Specialist II

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
Creator III
Creator III

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
Champion III
Champion III

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

joshrussin
Creator III
Creator III

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.