Microsoft SQL Server 2008 Bible

Handling dates and times is probably one of the most complicated and inconsistent topics in SQL. According to our personal experiences, operations with dates often cause confusion and even frustration not only among database users, but in the developers' community, too.

Introduction to complex data types

One of the problems is dates are not actually what they look like. So far we were talking only about simple data types that store one value per row. Date and time data types hold a number of elements (year, day, month, hour, etc.). In programming, such data types are called complex and are often represented as structures. When returned as a database query result, date and time fields appear like strings, but in fact they rather are parts of structures, similar to ones in the example below. (We don't use any specific programming language in this example, but rather some kind of pseudocode.)

STRUCTURE DATE { YEAR DECIMAL(4,0), MONTH DECIMAL(2,0), DAY DECIMAL(2,0) }

STRUCTURE TIME { HOUR DECIMAL(2,0), MINUTE DECIMAL(2,0), SECOND DECIMAL(5,2) }

STRUCTURE DATETIME { YEAR DECIMAL(4,0), MONTH DECIMAL(2,0), DAY DECIMAL(2,0), HOUR DECIMAL(2,0), MINUTE DECIMAL(2,0), SECOND DECIMAL(5,2) }

The displayed value just formats and concatenates the fields of this structure. For example, for the YYYY/DD/MM format, the pseudocode may look like this:

CONCAT(CAST(DATETIME.YEAR, STRING), '/', CAST(DATETIME.DAY, STRING), '/', CAST(DATETIME.MONTH, STRING))

These structures should also have some methods to handle situations when users want to display dates and times in different formats, for example to display time on the 12- or 24-hour scale, show day of week for a certain date, display century, convert it into a different time zone, and so on.

We'll talk more about complex data types later in this chapter.

Date and time implementations

As we've mentioned before, date and time data types are mandated by SQL99 and handled by different RDBMS implementations quite in a different way. Date and time data types are summarized in Table 3-5.

SQL99

SQL99 supports DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, and TIMESTAMP WITH TIME ZONE data types.

• DATE data type is a structure that consists of three elements: year, month, and day. The year is a four-digit number that allows values from 0000 through 9999; the month is a two-digit element with values from 01 through 12; and the day is another two-digit figure with range from 01 through 31. SQL99 does not have any strict rules on how to implement DATE internally, so vendors can make their own decisions. One vendor could choose something similar to the structures above; others could implement characters, numbers with different scale, and so on.

• TIME consists of hour, minute, and second components. The hour is a number from 00 to 23, the minute is a two-digit number from 00 to 59, and the second is either another integer from 00 to 61 or a decimal number with scale of 5 and precision of 3 that can hold values from 00.000 to 61.999.

  Note

  The range of values for seconds greater than 59 is to handle the representation of leap seconds, occasionally added to Earth's time. None of our three major vendors has that feature implemented.

• TIMESTAMP is a combination of DATE and TIME data types and includes year, month, day, hour, minute, and second.

• TIME WITH TIME ZONE is basically an improvement to the TIME data type. It stores time zone information in addition to standard TIME elements.

• TIMESTAMP WITH TIME ZONE is an extension to the TIMESTAMP with information on time zone.

Oracle 9i

Oracle has DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types.

• DATE is a slightly confusing data type, because in spite of its name it stores both date and time information and rather corresponds to SQL99 TIMESTAMP. In addition to standard SQL99 TIMESTAMP fields (year, month, date, hour, minute, and second), it also includes century.

• TIMESTAMP data type is practically same as DATE, but you can specify an optional precision for the number of digits in the fractional part of seconds. The valid values are numbers from 0 to 9 with the default of 6.

• TIMESTAMP WITH TIME ZONE data type speaks for itself. The only difference from TIMESTAMP is a time zone displacement included in its value, which is the difference in hours and minutes between local time and Coordinated Universal Time (UTC), also known as Greenwich Mean Time (GMT).

• TIMESTAMP WITH LOCAL TIME ZONE data type is another variation of TIMESTAMP (or TIMESTAMP WITH TIME ZONE). The difference is that it is normalized to the database time zone and the displacement value is not stored with it, but when users query the column, the result is returned in user's local session time zone.

  Understanding TIMESTAMP WITH LOCAL TIME ZONE Data Type

  This example will help you to understand the difference between TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE data types.

  Imagine ACME expanded, and it now has its offices all around the world. John is a head of Sales department located in Seattle, WA, and needs to see all new invoices created yesterday at each location. (We assume each location has its own database, and the databases can communicate to each other.) The problem is, one of the offices is in New York, another one is in Amsterdam, yet another is located in Bangkok, and so on; of course, each location is in its own time zone. But as far as John is concerned, he wants to see all new orders created whatever is considered to be yesterday in Seattle, in other words, according to the Pacific Standard Time (PST). Each of the mentioned offices belongs to a different time zone, without even mentioning the Daylight Savings. So, 08/31/2003 6:00 PM in Seattle is equivalent to 08/31/2003 9:00 PM in New York, 09/01/2003 3:00 AM in Amsterdam, and 09/01/2003 8:00 AM in Bangkok. That means not only different days, but even different months!

  If the data type of ORDER_HEADER.ORDHDR_INVOICEDATE_D column is TIMESTAMP, John would need a report that programmatically translates all the different locations' invoice dates into PST.

  Life would definitely be easier if we declared ORDHDR_INVOICEDATE_D as a TIMESTAMP WITH LOCAL TIME ZONE — then each time John queries a remote database from Seattle the result is returned in his session's time, that is, invoices created in Bangkok at 8 AM on September 1 (Thailand local time) will appear as belonging to August 31 Pacific Standard Time.

• INTERVAL YEAR TO MONTH data type stores a period of time using year and month fields.

• INTERVAL DAY TO SECOND data type can store a period of time in days, hours, minutes, and seconds.

  Caution

  The Oracle format value for minutes is MI, not MM like most other databases. So, be aware that if you want specify a time format it should look like HH:MI:SS, not HH:MM:SS. MM in Oracle stands for months.

DB2 UDB 8.1

DB2 has three standard SQL99 date and time data types — DATE, TIME, and TIMESTAMP.

• DATE consists of year, month, and day. The range of the year part is 0001 to 9999; month can be from 1 to 12; and the day part ranges from 1 to 28, 29, 30, or 31, depending on the month. DATE in DB2 is stored internally as a string of four bytes. Each byte represents two decimal digits. The first two bytes are for the year, the third is reserved for the month, and the fourth one holds day value. The length of a DATE column is 10 bytes to fit character string representation in literals.

• TIME data type is represented internally as a string of three bytes — one byte for hours, one for minutes, and one for seconds. Hour ranges from 0 to 24 (if value is 24, minutes and seconds will be all zeroes). Minute and second components have to be from 0 through 59. The length of a TIME column in DB2 is 8 bytes to allow the appropriate length for a character string representation.

• TIMESTAMP data type is a combination of DATE and TIME elements plus a microsecond component. The internal representation is a string of ten bytes (four DATE bytes, three TIME bytes, and additional three bytes for microseconds). The length of a TIMESTAMP column is 26 bytes.

MS SQL Server 2000

MS SQL Server has two date and time data types, DATETIME and SMALLDATETIME that both represent the combination of date and time values, but have different ranges.

• DATETIME can store values from 01/01/1753 to 12/31/9999 with accuracy of 0.00333 seconds. It is stored internally as an eight-byte string. The first four bytes represent the number of days before (or after) 01/01/1900, which is the system reference date. The second four bytes store time in milliseconds passed since midnight.

• SMALLDATETIME can hold dates from January 1, 1900 to June 6, 2079, with accuracy to the minute. The internal storage for that data type is four bytes. Again, the first portion (two bytes) stores the number of days after the system reference date (01/01/1900), and the second portion stores time (in minutes after midnight).

Date and time literals

Date and time literals are implementation-specific and vary significantly among different vendors.

Oracle 9i

Oracle lets you specify DATE values as literals if they match special database initialization parameter NLS_DATE_FORMAT, which defaults to DD-MON-YY. (Oracle initialization parameters are usually handled by DBA and are not covered in this book.) You can also use SQL99 literal standard (YYYY-MM-DD) with a DATE prefix, or convert literals into string using the Oracle function TO_DATE (covered in Chapter 10). The three following statements are valid date literals examples. (SHIPMENT_ARRIVDATE_D is a DATE field in Oracle ACME database.)

UPDATE shipment SET shipment_arrivdate_d = '03-SEP-02' WHERE shipment_id_n = 30661;

UPDATE shipment SET shipment_arrivdate_d = DATE '2003-09-02' WHERE shipment_id_n = 30661;

UPDATE shipment SET shipment_arrivdate_d = TO_DATE('September, 02 2003', 'Month, DD YYYY') WHERE shipment_id_n = 30661;

But these are illegal:

UPDATE shipment SET shipment_arrivdate_d = 'YYYY-MM-DD' WHERE shipment_id_n = 30661;

UPDATE shipment SET shipment_arrivdate_d = '03-SEP-02 23:12:45' WHERE shipment_id_n = 30661;

TIMESTAMP and TIMESTAMP WITH TIME ZONE data types also accept the DD-MON-YY format for literals (with optional TIME part); in addition, you can specify literals with a TIMESTAMP prefix:

TIMESTAMP '1997-01-31 09:26:50.124' TIMESTAMP '1997-01-31 09:26:56.66 +02:00' TIMESTAMP '1999-04-15 8:00:00 -8:00' TIMESTAMP '1999-04-15 8:00:00 US/Pacific' TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

The first of these lines is for TIMESTAMP data type, and the other four are for TIMESTAMP WITH TIME ZONE.

Oracle gives you a great deal of flexibility when specifying interval values as literals. Reference Table 3-6 for examples.

DB2 UDB 8.1

The following formats for DATE literals are recognized: YYYY-MM-DD (ANSI/ISO), MM/DD/YYYY (IBM US), and DD.MM.YYYY (IBM Europe).

So, these three statements are legal:

UPDATE shipment SET shipment_arrivdate_d = '2003-09-02' WHERE shipment_id_n = 30661

UPDATE shipment SET shipment_arrivdate_d = '09/02/2003' WHERE shipment_id_n = 30661

UPDATE shipment SET shipment_arrivdate_d = '02.09.2003' WHERE shipment_id_n = 30661

But this one is not:

UPDATE shipment SET shipment_arrivdate_d = '02-SEP-2003' WHERE shipment_id_n = 30661

The valid TIME literal formats are: HH.MM.SS (ANSI/ISO and IBM Europe), HH:MM AM|PM (IBM USA), and HH:MM:SS (Japanese Industrial Standard). Also, trailing blanks may be included, and a leading zero may be omitted from the hour part of the time; seconds may be omitted entirely:

'12.23.56' '23:15 AM' '8:45'

The valid string formats for TIMESTAMP literals are YYYY-MM-DD-HH.MM.SS.NNNNNN and YYYY-MM-DD HH:MM:SS.NNNNNN:

UPDATE shipment SET shipment_createdate_d = '2003-10-12-23.34.29' WHERE shipment_id_n = 30661

UPDATE shipment SET shipment_createdate_d = '2003-10-12 23:34:29.345678' WHERE shipment_id_n = 30661

MS SQL Server

MS SQL Server is probably the friendliest RDBMS in terms of handling date and time. It recognizes the date and time literals enclosed in single quotation marks in many different formats. For example:

'August 15, 2003' '15 August, 2003' '15-AUG-2003' '15 Aug, 2003' '030815' '2003/08/15' '08/15/03' '14:30:24' '04:24 PM' '15 August, 2003 23:00' '15-AUG-2003 22:45:34.345'

All these (and many other) formats are valid for both DATETIME and SMALLDATETIME.