topic Date and Time in QlikView

Date and Time

Fri, 23 Mar 2012 09:47:18 GMT

Hi!

I have a date field which is having time also.Now i want to create a field out of it in which the date should be the same but the time corresponding to each date should be 17:00:00

Date and Time

jvitantonio — Fri, 23 Mar 2012 09:53:59 GMT

Hi, here's the solution

Tab:

LOAD date(Date, 'DD/MM/YYYY hh:mm:ss') as Date INLINE [

Date

11/12/2010 11:00:00

11/12/2010 12:00:00

];

tab2:

load date(Date,'DD/MM/YYYY 17:00:00') AS Date1 Resident Tab;

Date and Time

jagan — Fri, 23 Mar 2012 11:36:29 GMT

Hi Rahul,

Try this script

Temp:

LOAD date#(Date, 'DD/MM/YYYY hh:mm:ss') as Date

INLINE [

Date

11/12/2010 11:00:00

11/12/2010 12:00:00

];

Data:

load date(Date,'DD/MM/YYYY 17:00:00') AS Date1 Resident Tab;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

Date and Time

Fri, 23 Mar 2012 12:20:43 GMT

this gives me the date with time stamp as 17:00 hrs but when i am doing some comparision internally the time stamp is 00:00:00 hrs .so i am not able to do a comparision with other date on the basis of time stamp as 1700 hrs.

pls let me know in case the requirement is not clear

Date and Time

hic — Fri, 23 Mar 2012 12:29:08 GMT

All date fields are numbers behind the scenes, and the Date() function will format the number as a date - but it will not change the number itself. So the above solution will make it look like 17:00 when it in fact really isn't.

A better way to do it is to first cut off the existing fractional number - the time of the day - so that you get an integer that corresponds to the date: floor(Date)

Then you add your 17 hours, e.g.: floor(Date) + 17/24 or floor(Date) + Time#('17:00',hh:mm')

And finally you format it as a date again: Date(floor(Date) + 17/24)

/HIC

Date and Time

jagan — Fri, 23 Mar 2012 12:34:21 GMT

Hi,

Check this script

Temp:

LOAD date#(Date, 'DD/MM/YYYY hh:mm:ss') as Date

INLINE [

Date

11/12/2010 11:00:00

11/12/2010 12:00:00

];

Data:

load date(floor(Date) + 17/24 ,'DD/MM/YYYY hh:mm:ss') AS Date1 Resident Temp;

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.