How can I execute something like:

select Current_Date() + Current_Time();

and get a timestamp?

I can use:

select Cast(Current_Date() as TimeStamp) + ???;

But how do I convert the Time to a fraction of a day?

TIA
Barry

Ok, I solved the problem.

I have to format the date and time into a VarChar that looks like a TimeStamp and then convert it to an actual TimeStamp.

select Cast(Cast(Current_Date() as VarChar) + ' ' + Cast(Current_Time() as VarChar) as Timestamp);

Barry

Barry

Why are you not just using the Constant "Current_Timestamp"?

i.e.

WHERE
 DateField >= Current_Timestamp


Is it maybe because you have a Time field & a Date field & you are trying to concatenate them? In that case your solution is what I would do.

Adam,

It's because I'm using Time of day variables in a script and need to add time to the current date. I thought there should be some arithmetic operation I could use to add a Date and Time variable to get a TimeStamp. But I had to resort to string concatenation to get it done with a Cast. It is a weird way of doing it but at least it works.

Barry

Barry


Date and time arithmetic is all down to INTERVAL now. This would probably be more of a pain to build but

CURRENT_DATE + INTERVAL '7' HOUR + INTERVAL '23' MINUTE,

would work

Roy Lambert

>Date and time arithmetic is all down to INTERVAL now. This would probably be more of a pain to build but

CURRENT_DATE + INTERVAL '7' HOUR + INTERVAL '23' MINUTE,

would work<

Roy,

How would it work in a script with 3 variables:

Declare _CurDate Date Default Current_Date();
Declare _CurTime TIme Default Current_Time();
Declare _CurDateTime  TimeStamp Default NULL;

set _CurDateTime = ??

Barry

Barry

>How would it work in a script with 3 variables:
>
>Declare _CurDate Date Default Current_Date();
>Declare _CurTime TIme Default Current_Time();
>Declare _CurDateTime TimeStamp Default NULL;
>
>set _CurDateTime = ??

Pretty much exactly the same, if you can build the script from outside the script but I suspect you want to build it within the script.

set _CurDateTime = _CurDate + INTERVAL '7' HOUR + INTERVAL '23' MINUTE;

It isn't possible to build this sort of statement dynamically since the INTERVAL parameters have to be a constant and EXECUTE IMMEDIATE doesn't work with SET statements

Roy Lambert

Roy Lambert wrote:

Barry

>>How would it work in a script with 3 variables:
>>
>>Declare _CurDate Date Default Current_Date();
>>Declare _CurTime TIme Default Current_Time();
>>Declare _CurDateTime TimeStamp Default NULL;
>>
>>set _CurDateTime = ??

>Pretty much exactly the same, if you can build the script from outside the script but I suspect you want to build >it within the script.

>set _CurDateTime = _CurDate + INTERVAL '7' HOUR + INTERVAL '23' MINUTE;

>It isn't possible to build this sort of statement dynamically since the INTERVAL parameters have to be a >constant and EXECUTE IMMEDIATE doesn't work with SET statements

So how do people add Time to a Date or TimeStamp variable in a script?

If I have a Date and Time variable in a script (shown above), how to do I add them together to get a TimeStamp value that I can assign to the variable _CurDateTIme?

From what I've experienced in the past week, I think it would be more productive for me to do brain surgery (on myself?) using kitchen utensils wearing oven mitts than to add a Time variable to a Date variable in an EDB script.  

Barry

Barry

>So how do people add Time to a Date or TimeStamp variable in a script?
>
>If I have a Date and Time variable in a script (shown above), how to do I add them together to get a TimeStamp value that I can assign to the variable _CurDateTIme?
>


You already found the answer - CAST this will work in the SET statement

>From what I've experienced in the past week, I think it would be more productive for me to do brain surgery (on myself?) using kitchen utensils wearing oven mitts than to add a Time variable to a Date variable in an EDB script.

If you think you're having trouble you need to track down a discussion Tim and I had about it. INTERVALs are another area me and the SQL standards committee will just never agree on.

Roy Lambert

Barry

>>From what I've experienced in the past week, I think it would be more productive for me to do brain surgery (on >>myself?) using kitchen utensils wearing oven mitts than to add a Time variable to a Date variable in an EDB script.  >>Smile

!!! Agreed. However this is partly because EDB sticks to the "rules" of SQL ... so really you have to blame them (as Roy says!)

I would suggest writing once an "AddDateToTime" function, which accepts a date and a time. You can then reference this in any script / statement and never have to perform the brain surgery!

Here is code for such a FUNCTION:

CREATE FUNCTION "AddDateToTime" (IN "aDate" DATE, IN "aTime" TIME)
RETURNS TIMESTAMP
BEGIN
 DECLARE Result TIMESTAMP;
SET Result = CAST(CAST(aDate as VarChar) + ' ' + Cast(aTime as VarChar)
                 as Timestamp);
RETURN Result;
END