Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Chanty4u
MVP
MVP

Not applicable
Author

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

swuehl
MVP
MVP

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.

sunny_talwar

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.

swuehl
MVP
MVP

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

reddy-s
Master II
Master II

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
Author

Hi reddy

I got it.