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

asked 13 Jun '13, 09:40

raphael's gravatar image

raphael
1515614
accept rate: 33%

edited 13 Jun '13, 10:56

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274


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'
permanent link

answered 13 Jun '13, 10:46

raphael's gravatar image

raphael
1515614
accept rate: 33%

edited 13 Jun '13, 10:46

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;
permanent link

answered 13 Jun '13, 10:53

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274
accept rate: 40%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×15

question asked: 13 Jun '13, 09:40

question was seen: 1,161 times

last updated: 13 Jun '13, 10:56