Dates and Times

Dates and Times

Implementations of SQL normally support dates and times as special data types. These values are often representable in a variety of formats such as 5/14/1948 or 14 May 1948. Here we shall explain only the SQL standard notation, which is very specific about format.

A date constant is represented by the keyword DATE followed by a quoted string of a special form. For instance, DATE '1948- 05-14' follows the required form. The first four characters are digits representing the year. Then come a hyphen and two digits representing the month. Note that, as in our example, a one-digit month is padded with a leading 0. Finally there is another hyphen and two digits representing the day. As with months, we pad the day with a leading 0 if that is necessary to make a two-digit number.

A time constant is represented likewise by the keyword TIME and a quoted string. This string has two digits for the hour, on the military (24-hour) clock. Then come a colon, two digits for the minute, another colon, and two digits for the second. If fractions of a second are desired, we may continue with a decimal point and as many significant digits as we like. For example, TIME '15:00: 02.5' represents the time at which all students will have left a class that ends at 3 PM: two and a half seconds past three o'clock.

Escape Characters in LIKE expressions

On the other hand, time can be expressed as the number of hours and minutes ahead of (indicated by a plus sign) or behind (indicated by a minus sign) Greenwich Mean Time (GMT). For instance, TIME '12:00:00-8:00' represents noon in Pacific Standard Time, which is eight hours behind GMT.

To combine dates and times we use a value of type TIMESTAMP. These values consist of the keyword TIMESTAMP, a date value, a space, and a time value. Therefore, TIMESTAMP '1948-05-14 12:00:00' represents noon on May 14, 1948.

We can compare dates or times using the same comparison operators we use for numbers or strings. That is, < on dates means that the first date is earlier than the second; < on times means that the first is earlier (within the same day) than the second.