Search

PowerBuilder & MS SQLServer datetime methods

Posted on Tuesday, July 6th, 2010 at 8:37 pm in

If you have scheduling functionality in your application sooner or later you are faced with spanning dates. By this I mean starting something on one day and ending it on another. Powerbuilder is not very good at this, especially if you have something which starts before midnight and ends after midnight. SQL Server provides good functionality for date manipulation. Here are a couple of functions you may find useful.

f_datetime_after (datetime adt, long, al_amt, string as_datepart)

//returns the datetime after a set interval from a starting datetime
//cannot use powerbuilder since it cannot span days
// valid dateparts are:
/*Year
quarter
month
dayofyear
day
week
weekday
hour
minute
second
millisecond
microsecond
nanosecond*/
string ls_sql
datetime ldt_end
DECLARE get_diff DYNAMIC CURSOR FOR SQLSA ;
// Calculate the end date
ls_sql = 'SELECT DATEADD(' + as_datepart + ', ' + string(al_amt) +  ", '" + string(adt) + "')"
PREPARE SQLSA FROM :ls_sql ;
OPEN DYNAMIC get_diff ;
FETCH get_diff INTO :ldt_end ;
CLOSE get_diff ;
RETURN ldt_end
f_difference_in_minutes (datetime adt_start, datetime adt_end)
//returns the difference between the two times in minutes.
long ll_dur_qty
string ls_sql
DECLARE get_seconds_diff DYNAMIC CURSOR FOR SQLSA ;

// Calculate the difference
ls_sql= 'select DateDiff(ss, "' + string(adt_start) + '", "' + string(adt_stop) + '")'
PREPARE SQLSA FROM :ls_sql ;
OPEN DYNAMIC get_seconds_diff ;
FETCH get_seconds_diff INTO :ll_dur_qy ;
CLOSE get_seconds_diff ;

ll_dur_qy = ll_dur_qy / 60 // convert to minutes
RETURN ll_dur_qty
Tags:

Top