Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

Timestamp

Hi,

how to convert time stamp into date format(e.g.: 2/22/2016  12:00:00 AM) this is the date and time i want date as separate and time as separate ... can you  please  check it i was give like that but i didn't get the result

   date(date#( OrderDate, 'dd/mm/yyyy hh:mm:ss'),'DD-MMM-YYYY')  as Date,

  time(Time#( OrderDate, 'dd/mm/yyyy hh:mm:ss'),'hh:mm') as time

regards.

Sai Madhu

Reddys310

gwassenaar

Ogster1974

mtoNaveen06

7 Replies
sureshqv
Esteemed Contributor III

Re: Timestamp

Not applicable

Re: Timestamp

Still i didn't get it can u give any example 

MVP
MVP

Re: Timestamp

Here is a script sample:

LOAD *,
Date(Floor(Timestamp#(OrderDate,'M/D/YYYY hh:mm:ss TT')),'DD-MM-YYYY') as Date,
Time(Frac(Timestamp#(OrderDate,'M/D/YYYY hh:mm:ss TT')),'hh:mm') as Time;
LOAD * INLINE [
OrderDate
2/22/2016 12:00:00 AM
2/28/2016 01:15:45 PM
]
;

You need to use the correct format code for the interpretation function Timestamp#() or Date#(), note that 'mm' denotes minutes, not month. See the help for a complete overview of the format codes.

Then use Floor() resp. Frac() to only retrieve the integer part of your timestamp value for date resp. decimal places for time.

Use a formatting function Date() resp. Time() to format as you like.

MVP
MVP

Re: Timestamp

In addition to what Stefan mentioned here you can also set TimeStamp, Date, Time Formats up top so that you don't have to use TimeStamp#, Date#, Time# again and again. Something like this:

SET TimeFormat='hh:mm';

SET DateFormat='DD-MMM-YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss TT'; //Match this with the dates you have right now and make sure to use upper case M for Month (Seems your timestamp may have 2-3 spaces between the date and time so you might have to fix this accordingly.

Once you have done this, you can simply do this using Stefan's example:

LOAD *,
          Date(Floor(OrderDate)) as Date,
          Time(Frac(OrderDate)) as Time;
LOAD * INLINE [
OrderDate
2/22/2016 12:00:00 AM
2/28/2016 01:15:45 PM
]
;

But in case you have multiple Timestamps than using the TimeStamp# would make sense.

MVP
MVP

Re: Timestamp

Sai Madhu, just remember that there are three steps involved:

a) Value interpretation, explicitely or automatic

Data Types in QlikView

Automatic Number Interpretation

The Date Function

Why don’t my dates work?

b) Transformations

The Master Calendar

The Master Time Table

[master dimension tables shown here as examples for transformations, but also have a look at the general concept]

c) Value formatting

The Date Function

On Format Codes for Numbers and Dates

Get the Dates Right

reddys310
Honored Contributor II

Re: Timestamp

Hi Sai Madhu,

Also, adding an other point here:

With the latest Data manager, you can make use of the date field conversion to change a field to a date field. This will automatically generate a master calendar for you. A very handy addition.

Hope this helps!

Thanks,

Sangram.

Not applicable

Re: Timestamp

Hi reddy

I got it.