topic Re: Timestamp in App Development

Timestamp

Sun, 28 Feb 2016 09:44:42 GMT

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‌

mto‌Naveen06‌

Re: Timestamp

Chanty4u — Sun, 28 Feb 2016 09:54:36 GMT

chk dis

Change from DATETIME to DATE

Re: Timestamp

Sun, 28 Feb 2016 10:13:27 GMT

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

Re: Timestamp

swuehl — Sun, 28 Feb 2016 11:06:39 GMT

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.

Re: Timestamp

sunny_talwar — Sun, 28 Feb 2016 12:29:39 GMT

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.

Re: Timestamp

swuehl — Sun, 28 Feb 2016 14:36:38 GMT

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

Re: Timestamp

reddy-s — Mon, 29 Feb 2016 10:00:48 GMT

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.

Re: Timestamp

Mon, 29 Feb 2016 10:33:16 GMT

Hi reddy

I got it.