在PB中計算指定日期相對數量單位前(后)的日期

通過靈活使用PB提供的RelativeDate函數還是比較容易實現的，RelativeDate函數要求提供的第一個參數必須為date類型，也即該函數只能處理日期 （不包含時間）運算，因此有關時間部分的運算只能分開執行。以下是f_dateadd函數完整代碼，在PB8/9上測試通過。
/*******************************************************************
函數名稱：f_dateadd()
參數說明:as_datepart string 日期單位,可取如下值:
                            year,yyyy,yy,y,年:年;
            quarter,qq,季:季;
                            month,mm,m,月:月;
                            day,dd,天:天;       
            week,wk,ww,星期,周:星期;
                            hour,hh,h,小時:小時
            minute,mi,分:分鐘;
                            second,ss,秒:秒;
                            millisecond,ms,毫秒:毫秒
         al_number long 相對數量,取后來日期,為正值, 取前置日期,為負值    
         adt_refdate datetime 參考日期                      
返回值：datetime 相對數量單位前(后)的日期
          若三個參數中有一個為空,則返回空值,若輸入的日期單位非要求的單位也返回空值
功能描述：計算指定日期相對數量單位前(后)的日期
創建日期：2006-07-27
版本號：V1.0
*******************************************************************/
datetime ldt_return
string ls_sql
any la_values[]
long ll_year,ll_month,ll_day,ll_hour,ll_minute,ll_second,ll_millisecond
long ll_day_tmp,ll_hour_tmp,ll_minute_tmp,ll_second_tmp,ll_millisecond_tmp
date ld_return
time lt_return
 
//檢查參數是否有效
if isnull(as_datepart) or isnull(al_number) or isnull(adt_refdate) then
       setnull(ldt_return)
       return ldt_return
end if
if al_number = 0 then return adt_refdate
 
//取各部分時間
ll_year = year(date(adt_refdate))
ll_month = month(date(adt_refdate))
ll_day = day(date(adt_refdate))
ll_hour = hour(time(adt_refdate))
ll_minute = minute(time(adt_refdate))
ll_second = second(time(adt_refdate))
ll_millisecond = long(rightw(string(adt_refdate,'yyyy-mm-dd hh:mm:ss.fff'),3))
 
//根據不同的日期單位進行計算
choose case lower(as_datepart)
case "year","yyyy","yy","y","年"//年
       ll_year = ll_year + al_number
       if not isdate(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day)) then
              do while isdate(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day))
                     ll_day --
              loop
       end if
       ld_return = date(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day))
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case "quarter","qq","q","季"//季                   
       ll_year = ll_year + long((al_number * 3 + ll_month) / 12) - 1
       if mod(al_number * 3 + ll_month,12) = 0 then
              ll_month = 12
       else
              ll_month = mod(al_number * 3 + ll_month,12)
       end if                  
       if not isdate(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day)) then
              do while isdate(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day))
                     ll_day --
              loop
       end if
       ld_return = date(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day))
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case "month","mm","m","月"//月          
       if al_number > 0 then
              ll_year = ll_year + al_number / 12
       else
              if mod(abs(al_number),12) = 0 then
                     ll_year = ll_year + al_number / 12
              else
                     ll_year = ll_year + al_number / 12 + 1
              end if
       end if
       ll_month = ll_month + mod(al_number,12)
       if ll_month > 12 then
              ll_year = ll_year + 1
              ll_month = ll_month - 12
       elseif ll_month = 0 then
              ll_year = ll_year - 1
              ll_month = 12                
       end if    
       if not isdate(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day)) then
              do while isdate(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day))
                     ll_day --
              loop
       end if    
       ld_return = date(string(ll_year) + "-" + string(ll_month) + "-" + string(ll_day))
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case "day","dd","d","日"//日
       ld_return = relativedate (date(adt_refdate),al_number)
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case "week","wk","ww","星期","周"//周
       return f_dateadd('day',al_number * 7,adt_refdate)
case "hour","hh","h","小時"//小時
       ld_return = relativedate (date(adt_refdate),truncate((al_number + ll_hour) / 24,0))          
       if al_number > 0 then
              ll_hour = mod(al_number + ll_hour,24)
       else
              ll_hour = 24 - mod(abs(al_number + ll_hour),24)
              ld_return = relativedate (ld_return,-1)
       end if
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case "minute","mi","n","分"//分鐘
       if ll_hour * 60 + ll_minute + al_number < 0 then
              ll_day_tmp = ceiling(abs(ll_hour * 60 + ll_minute + al_number) / 60 / 24) * (-1)
       else
              ll_day_tmp = (ll_hour * 60 + ll_minute + al_number) / 60 / 24
       end if
       ld_return = relativedate (date(adt_refdate),ll_day_tmp)
       ll_hour_tmp = (ll_hour * 60 + ll_minute + al_number - ll_day_tmp * 24 * 60) / 60
       ll_minute = ((ll_hour * 60 + ll_minute + al_number - ll_day_tmp * 24 * 60) / 60 - ll_hour_tmp) * 60
       ll_hour = ll_hour_tmp
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case "second","ss","s","秒"//秒
       if ll_hour * 60 * 60 + ll_minute * 60 + ll_second + al_number < 0 then
              ll_day_tmp = ceiling(abs(ll_hour * 60 * 60 + ll_minute * 60 + ll_second + al_number) / 60 / 60 / 24) * (-1)
       else
              ll_day_tmp = (ll_hour * 60 * 60 + ll_minute * 60 + ll_second + al_number) / 60 / 60 / 24
       end if
       ld_return = relativedate (date(adt_refdate),ll_day_tmp)
       ll_hour_tmp = (ll_hour * 60 * 60 + ll_minute * 60 + ll_second + al_number - ll_day_tmp * 24 * 60 * 60) / 60 / 60
       ll_minute_tmp = (ll_hour * 60 * 60 + ll_minute * 60 + ll_second + al_number - ll_day_tmp * 24 * 60 * 60 - ll_hour_tmp * 60 * 60) / 60
       ll_second = ll_hour * 60 * 60 + ll_minute * 60 + ll_second + al_number - ll_day_tmp * 24 * 60 * 60 - ll_hour_tmp * 60 * 60 - ll_minute_tmp * 60
       ll_hour = ll_hour_tmp
       ll_minute = ll_minute_tmp
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case "millisecond","ms","毫秒"//毫秒
       //先換算成秒計算         
       if ll_millisecond + al_number < 0 then
              ll_second_tmp = ceiling(abs(ll_millisecond + al_number) / 1000) * (-1)
       else
              ll_second_tmp = (ll_millisecond + al_number) / 1000
       end if
       ll_millisecond = ll_millisecond + al_number - ll_second_tmp * 1000
     
       if ll_millisecond >= 1000 then
              ll_second_tmp = ll_second_tmp + 1
              ldt_return = f_dateadd('second',ll_second_tmp,adt_refdate)
              ll_millisecond = 1000 - ll_millisecond
       else
              ldt_return = f_dateadd('second',ll_second_tmp,adt_refdate)
       end if
       ld_return = date(ldt_return)
       ll_hour = hour(time(ldt_return))
       ll_minute = minute(time(ldt_return))
       ll_second = second(time(ldt_return))
       lt_return = time(string(ll_hour,'00')+":"+string(ll_minute,'00')+":"+string(ll_second,'00')+"."+string(ll_millisecond,'000'))
case else//無效參數
       setnull(ldt_return)
       return ldt_return
end choose
 
ldt_return = datetime(ld_return,lt_return)
return ldt_return

摘自 忘記閣