TimeStamp addition

[Liam1]

Hello,

I have a problem with TimeStamp arithmatic.

I am trying to add a date and a time to gve me a time stamp.

Basically if i cast the date as a timestamp it will give me a time stamp of
2002-09-21 00:00:00
I want to add to it, the time
00:57:17.000000
(i can format the seonds later)

so that i get a time stamp of
2002-09-21 00:57:17.000000
I don't know much about intervals etc to get this to work!

Thanks,

Liam

 

[tdatgod]

Hi,
I can't find anything in the manual that says you can ADD 2 time stamps. You can subtract them but that gives you an interval? The Best I can see is make the first a Timestamp and the second and interval.


cast(Date as Timestamp(6)) + cast(mytime as Interval Hour(2) to Second(6))


the hour(2) says you want 2 digits maximum for Hour and the second(6) means you want 6 place accuracy on your microseconds. for maximum flexibility you could use

hour(4) to Second(6).

Now you didn't say where the time was coming from so I assumed it was a column in your table like mytime.

However if you meant to have the current date and time as a time stamp, there is a SQL constant

CURRENT_TIMESTAMP

which will return the current date and time in time stamp format.


please see

Thread328-150396

for more information on the ANSI interval operators.

 

[Liam1]

Thanks for the above,

I ahve tried this originally, but seemed to be making a hash!

I could get the actualy Date no problem, it's trying to get the time which is causing grief.
The below SQL does not work
select cast (current_TimeStamp as Interval Hour(2) to Second(6))

the message
Invalid Operation on ANSI DateTime or Interval Value
appears.

Is this anything to do with runing V2R3 at the moment?
V2R4 will arrive next week!

Thanks,

Liam

 

[dnoeth]

You can't add a time to a date, you have to cast it to an Interval.
If your time-column is a properly formatted char then just:
cast(timecol as hour to second)

If the datatype is TIME then this is easier:
select
cast(current_date as timestamp(2)) +
((current_time - time '00:00:00') hour to second(2))

Dieter