Chapter 7 SQL Data Types
279
♦
timestamp + integer Add the specified number of days to a date or
timestamp.
♦
timestamp - integer Subtract the specified number of days from a
date or timestamp.
♦
date - date Compute the number of days between two dates or
timestamps.
♦
date + time Create a timestamp combining the given date and time.
Comparing dates and times in the database
By default, values stored as DATE do not have any hour or minute values,
and so comparison of dates is straightforward.
If you set the TRUNCATE_DATE_VALUES option to OFF, then the DATE
data type also contains a time, which introduces complications when
comparing dates. If the time is not specified when a date is entered into the
database, the time defaults to 0:00 or 12:00am (midnight). Any date
comparisons with this option setting compare the times as well as the date
itself. A database date value of ’1992-05-23 10:00’ is not equal to the
constant ’1992-05-23’. The DATEFORMAT function or one of the other date
functions can be used to compare parts of a date and time field. For example:
DATEFORMAT(invoice_date,’yyyy/mm/dd’) = ’1992/05/23’
If a database column requires only a date, client applications should ensure
that times are not specified when data is entered into the database. This way,
comparisons with date-only strings will work as expected.
If you wish to compare a date to a string as a string, you must use the
DATEFORMAT function or CAST function to convert the date to a string
before comparing.
Using unambiguous dates and times
Dates in the format yyyy/mm/dd or yyyy-mm-dd are always recognized
unambiguously as dates, regardless of the DATE_ORDER setting. Other
characters can be used as separators instead of "/" or "-"; for example, "?", a
space character, or ",". You should use this format in any context where
different users may be employing different DATE_ORDER settings. For
example, in stored procedures, use of the unambiguous date format prevents
misinterpretation of dates according to the user’s DATE_ORDER setting.
Also, a string of the form hh:mm:ss.sss is interpreted unambiguously as a
time.
To Next Page
Loading...