Skip to content

Date Calculations

 

You can use SQL to calculate on dates.

Following is a list of functions that can be used to manipulate dates in Firebird. (Click on the functions to open the Firebird reference manual)

  • dateadd() - Adds the specified number of years, months, days, hours, minutes, seconds or milliseconds to a date/time value
  • datediff() - Returns the number of years, months, days, hours, minutes, seconds or milliseconds elapsed between two date/time values.
  • extract() - Extracts and returns an element from a DATE, TIME or TIMESTAMP expression

 

Notes

Dates and times can be extracted from the server using

  • Date - select CURRENT_DATE from RDB$DATABASE
  • Time - select CURRENT_TIME from RDB$DATABASE
  • Timestamp - select CURRENT_TIMESTAMP from RDB$DATABASE

A constant date can be added to SQL in the following formats:

  • 'm/d/yyyy' - eg. '9/30/2015'
  • 'm.d.yyyy' - eg. '9.30.2015'
  • 'd mmm yyyyy' - eg. '30 Sep 2015'

Calculating the Start and End date of the current month.

select
(CURRENT_DATE - extract(day from CURRENT_DATE) + 1) as STARTOFTHEMONTH,
CURRENT_DATE - extract(day from CURRENT_DATE) + 32 - extract(day from
  (CURRENT_DATE - extract(day from CURRENT_DATE) + 32)) as ENDOFTHEMONTH
from RDB$DATABASE

Extracting a three-month summary based on the current date.

select
MON,
YEA,
(select sum(L.TOTAL)
  from LEDGER L
  where L.ENTRYTYPEID = 1
  and extract(month from L.TRANSDATE) = MON
  and extract(year from L.TRANSDATE) = YEA) as QUOTES,
  
(select sum(L.TOTAL)
  from LEDGER L
  where L.ENTRYTYPEID = 2
  and extract(month from L.TRANSDATE) = MON
  and extract(year from L.TRANSDATE) = YEA) as ORDERS,
  
(select sum(L.TOTAL)
  from LEDGER L
  where L.ENTRYTYPEID = 6
  and extract(month from L.TRANSDATE) = MON
  and extract(year from L.TRANSDATE) = YEA) as INVOICES
  
from
 
  (select
  extract(month from CURRENT_DATE) as MON,
  extract(year from CURRENT_DATE) as YEA
  from RDB$DATABASE
  union
  select
  extract(month from dateadd(-1 month to CURRENT_DATE)) as MON,
  extract(year from dateadd(-1 month to CURRENT_DATE)) as YEA
  from RDB$DATABASE
  union
  select
  extract(month from dateadd(-2 month to CURRENT_DATE)) as MON,
  extract(year from dateadd(-2 month to CURRENT_DATE)) as YEA
  from RDB$DATABASE
  )