Date and Time

[#1]

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

[jvitantonio]

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;

[jagan]

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.

[#4]

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

[hic · Accepted solution]

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

[jagan]

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.