DATA no SQL

Muitos sistemas fazem várias pesquisas e cálculos utilizando datas e horas. Existem várias formas de fazer isso em delphi, Java e outras linguagens, mas a melhor forma ainda é, no caso de uma adição fixa, fazer isso no SQL. Vou colocar aqui alguns exemplos de funções específicas para DATA e HORA que o firebird possui.

DATEADD()

Available in: DSQL, PSQL
Added in: 2.1
Description: Adds the specified number of years, months, days, hours, minutes, seconds or milliseconds to a date/time value.
Result type: DATE, TIME or TIMESTAMP
Syntax: 

DATEADD ()

args      ::=    TO 

                 | , , 

amount    ::=  an integer expression (negative to subtract)

unit      ::=  YEAR | MONTH | DAY

                   | HOUR | MINUTE | SECOND | MILLISECOND

datetime  ::=  a DATE, TIME or TIMESTAMP expression

·         The result type is determined by the third argument.

·         With DATE arguments, only YEAR, MONTH and DAY can be used.

·         With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

Examples: 

dateadd (28 day to current_date)

dateadd (-6 hour to current_time)

dateadd (month, 9, DateOfConception)

dateadd (minute, 90, time 'now')

dateadd (? year to date '11-Sep-1973')

DATEDIFF()

Available in: DSQL, PSQL
Added in: 2.1
Description: Returns the number of years, months, days, hours, minutes, seconds or milliseconds elapsed between two date/time values.
Result type: BIGINT
Syntax: 

DATEDIFF ()

     ::=   FROM  TO 

                | , , 

     ::=  YEAR | MONTH | DAY

                  | HOUR | MINUTE | SECOND | MILLISECOND

  ::=  a DATE, TIME or TIMESTAMP expression

·        DATE and TIMESTAMP arguments can be combined. No other mixes are allowed.

·        With DATE arguments, only YEAR, MONTH and DAY can be used.

·        With TIME arguments, only HOUR, MINUTE, SECOND and MILLISECOND can be used.

Computation: 

·         DATEDIFF doesn't look at any smaller units than the one specified in the first argument. As a result,

o        “datediff (year, date '1-Jan-2009', date '31-Dec-2009')” returns 0, but

o        “datediff (year, date '31-Dec-2009', date '1-Jan-2010')” returns 1

·         It does look, however, at all the bigger units. So:

o        “datediff (day, date '26-Jun-1908', date '11-Sep-1973')” returns 23818

·         A negative return value indicates that moment2 lies before moment1.

Examples: 

datediff (hour from current_timestamp to timestamp '12-Jun-2059 06:00')

datediff (minute from time '0:00' to current_time)

datediff (month, current_date, date '1-1-1900')

datediff (day from current_date to cast(? as date))

http://www.firebirdsql.org/