|
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 |
|
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' |
|
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; |