Re: [sqr-users] Adding minutes to date time field

[Don Mellen <donm@ontko.com>]

Rule 1: Never convert a date into a string unless necessary.
Rule 2: If you're database specific anyway, use it to your best advantage

example...


Begin-Select
a.strt_time                               &strt_time=date
nvl(a.stop_time,a.strt_time + (15/1440))  &stop_time=date
  ...
End-Select

INSERT INTO TABLE2 (start_time, stop_time) values (&strt_time, &stop_time)


... This assumes that strt_time always has a value.  
If you need both original values for other processing/logic, then....

Begin-Setup
   declare-variable
      DATE $stop_time
   end-declare
End-Setup

  ...
Begin-Select
a.strt_time    &strt_time=date
a.stop_time    &stop_time=date
  ...
End-Select

  ...
 let $StopTime = dateadd(&strt_time,'MINUTE',15)

  ...
INSERT INTO TABLE2 (start_time, stop_time) values (&strt_time, $stop_time)


HTH,
Don

On Fri, 26 Mar 2004, abhay sachar wrote:

> Hi,
> 
> I am selecting two fields from a table, Start Time and Stop Time.
> I have to add a logic that if Start Time has a value and Stop Time is null, 
> then I need to add 15 min. to the Start Time and load that calue in Stop 
> Time.
> Then, I need to insert these 2 values into another table.
> 
> I am trying to do this
> SELECT
> to_char(a.strt_time, 'MM/DD/YYYY HH:MI:SS AM')   &a.strt_time
> to_char(a.stop_time, 'MM/DD/YYYY HH:MI:SS AM')  &a.stop_time
> Let $StrtTime = &a.strt_time
> Let $StopTime = &a.stop_time
> from TABLE1.
> 
> Then,
> Let $StopTime = dateadd(strtodate($StrtTime),'MINUTE',15)
> 
> Then,
> INSERT INTO TABLE2 (start_time, stop_time) values (to_date($StrtTime, 
> 'MM/DD/YYYY HH:MI:SS AM'), to_date($StrtTime, 'MM/DD/YYYY HH:MI:SS AM')).
> 
> Data is like
> Start Tme = 10/02/2003 8:00 AM
> But it is not able to insert the 'converted' Stop Time since the conversion 
> makes it like '02-Oct-2003'. There is no time value in that.
> 
> Any help would be greatly appreciated.
> 
> Thanks,
> Abhay Sachar.
> 202-334-4381 (Work)
> 
> _________________________________________________________________
> Apply for a Citibank Suvidha Account.  http://go.msnserver.com/IN/45533.asp 
> Get FREE organiser. 
> 
> 
> _______________________________________________
> sqr-users mailing list
> sqr-users@sqrug.org
> http://www.sqrug.org/mailman/listinfo/sqr-users
> 

-----------------------------------------------------------------------
Donald Mellen  | Ray Ontko & Co. - Richmond, IN - http://www.ontko.com/
donm@ontko.com |  "In the beginning, there was nothing, which exploded"



_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users