How to do datetime computations in a stored procedure?

[Former Member]

I have a stored procedure similar to this. I'm trying to pass in the date '2013-05-06' and then add some different times to it, but I can't figure out how or if it's even possible:

alter procedure "test"."my_proc"(in business_date date, in i_emp_seq int)

begin
    declare qdtl_startdate datetime;
    declare qdtl_enddate datetime;
    declare ettl_startdate datetime;
    declare ettl_enddate datetime;

    set qdtl_startdate = business_date + ' 05:00:00'; -- trying to get '2013-05-06 05:00:00'
    set qdtl_enddate = dateadd(day, 1, qdtl_startdate); -- trying to get '2013-05-07 05:00:00'
    set ettl_startdate = business_date + ' 02:00:00'; -- trying to get '2013-05-06 02:00:00'
    set ettl_enddate = dateadd(day, 1, ettl_startdate) + ' 03:59:59'; -- trying to get '2013-05-07 03:59:59'

select
...

SQL Anywhere version: 9.0.2.3586

[MarkCulp]

I'm sure there are lots of ways of doing this, but one way is to cast the date to a string, concatenate the time string and then cast the resultant string back to a timestamp (or datetime). You have attempted to do this but you need to add some explicit casts. Example:

alter function add_time_to_date( in d date, in t varchar(10) )
returns timestamp
begin
     return cast( string( cast( d as varchar(16) ), ' ', t ) as timestamp );
end;

Example usage:

select add_time_to_date( cast( '2013-06-13' as date ), '5:06:07' );

returns the timestamp value 2013-06-13 05:06:07.000

Once you have your timestamp/datetime you can start to do your dateadd computations on the value.

alter procedure "test"."my_proc"(in business_date date, in i_emp_seq int)
begin
    declare qdtl_startdate datetime;
    declare qdtl_enddate datetime;
    declare ettl_startdate datetime;
    declare ettl_enddate datetime;

    set qdtl_startdate = cast( string( cast( business_date as varchar(16) ), ' 05:00:00' ) as datetime );
    set qdtl_enddate   = dateadd( day, 1, qdtl_startdate );
    set ettl_startdate = cast( string( cast( business_date as varchar(16) ), ' 02:00:00' ) as datetime ); 
    set ettl_enddate   = dateadd(day, 1, ettl_startdate) + ' 03:59:59'; 
...
end;

[Former Member]

After some more research & experimenting I was able to figure it out:

set qdtl_startdate = dateadd(hour,5,business_date); -- trying to get '2013-05-06 05:00:00'
set qdtl_enddate = dateadd(day, 1, qdtl_startdate); -- trying to get '2013-05-07 05:00:00'
set ettl_startdate = dateadd(hour,2,business_date); -- trying to get '2013-05-06 02:00:00'
set ettl_enddate = dateadd(day, 1, ettl_startdate); -- trying to get '2013-05-07 03:59:59'