InformIT: Sams Teach Yourself SQL in 24 Hours

Table of Contents

Date Conversions

The conversion of dates can take place for any number of reasons. Conversions are mainly used to alter the data type of values defined as a DATETIME value or any other valid data type of a particular implementation.

Typical reasons for date conversions are as follows:

To compare date values of different data types
To format a date value as a character string
To convert a character string into a date format

The ANSI CAST operator is used to convert data types into other data types.

The basic syntax is as follows:

   
CAST ( EXPRESSION AS NEW_DATA_TYPE )

Specific examples according to the syntax of some implementations are illustrated in the following subsections, covering

The representation of parts of a DATETIME value
Conversions of dates to character strings
Conversions of character strings to dates

Date Pictures

A date picture is composed of formatting elements used to extract date and time information from the database in a desired format. Date pictures may not be available in all SQL implementations.

Without the use of a date picture and some type of conversion function, the date and time information is retrieved from the database in a default format, such as

1999-12-31
31-DEC-99
1999-12-31 23:59:01.11
...

What if you wanted the date displayed as the following? You would have to convert the date from a DATETIME format into a character string format:

December 31, 1997

This is accomplished by implementation-specific functions for this very purpose, further illustrated in the following sections.

Sybase date pictures:
`yy`	Year
`qq`	Quarter
`mm`	Month
`dy`	Day of year
`wk`	Week
`dw`	Weekday
`hh`	Hour
`mi`	Minute
`ss`	Second
`ms`	Millisecond

Oracle date pictures:
`AD`	Anno Domini
`AM`	Ante meridian
`BC`	Before Christ
`CC`	Century
`D`	Number of the day in the week
`DD`	Number of the day in the month
`DDD`	Number of the day in the year
`DAY`	The day spelled out (MONDAY)
`Day`	The day spelled out (Monday)
`day`	The day spelled out (monday)
`DY`	The three-letter abbreviation of day (MON)
`Dy`	The three-letter abbreviation of day (Mon)
`dy`	The three-letter abbreviation of day (mon)
`HH`	Hour of the day
`HH12`	Hour of the day
`HH24`	Hour of the day for a 24-hour clock
`J`	Julian days since 12-31-4713 B.C.
`MI`	Minute of the hour
`MM`	The number of the month
`MON`	The three-letter abbreviation of the month (JAN)
`Mon`	The three-letter abbreviation of the month (Jan)
`mon`	The three-letter abbreviation of the month (jan)
`MONTH`	The month spelled out (JANUARY)
`Month`	The month spelled out (January)
`month`	The month spelled out (january)
`PM`	Post meridian
`Q`	The number of the quarter
`RM`	The Roman numeral for the month
`RR`	The two digits of the year
`SS`	The second of a minute
`SSSSS`	The seconds since midnight
`SYYYY`	The signed year; if B.C. 500, B.C. = -500
`W`	The number of the week in a month
`WW`	The number of the week in a year
`Y`	The last digit of the year
`YY`	The last two digits of the year
`YYY`	The last three digits of the year
`YYYY`	The year
`YEAR`	The year spelled out (NINETEEN-NINETY-NINE)
`Year`	The year spelled out (Nineteen-Ninety-Nine)
`year`	The year spelled out (nineteen-ninety-nine)

MySQL date pictures:
`SECOND`	Seconds
`MINUTE`	Minutes
`HOUR`	Hours
`DAY`	Days
`MONTH`	Months
`YEAR`	Years
`MINUTE_SECOND`	Minutes and seconds
`HOUR_MINUTE`	Hours and minutes
`DAY_HOUR`	Days and hours
`YEAR_MONTH`	Years and months
`HOUR_SECOND`	Hours, minutes, and seconds
`DAY_MINUTE`	Days and minutes
`DAY_SECOND`	Days and seconds

Converting Dates to Character Strings

DATETIME values are converted to character strings to alter the appearance of output from a query. A conversion function is used to achieve this. Two examples, the first using SQL Server, of converting date and time data into a character string as designated by a query follow:

   
   
   

   SELECT DATE_HIRE = DATENAME(MONTH, DATE_HIRE)

   FROM EMPLOYEE_PAY_TBL

   
DATE_HIRE
---------
May
June
August
June
July
Jan

6 rows affected.

The following is an Oracle date conversion using the TO_CHAR function:

   
   

   SELECT DATE_HIRE, TO_CHAR(DATE_HIRE,'Month dd, yyyy') HIRE

   FROM EMPLOYEE_PAY_TBL;

   
DATE_HIRE HIRE
--------- ------------------
23-MAY-89 May       23, 1989
17-JUN-90 June      17, 1990
14-AUG-94 August    14, 1994
28-JUN-97 June      28, 1997
22-JUL-96 July      22, 1996
14-JAN-91 January   14, 1991

6 rows selected.

Converting Character Strings to Dates

The following example illustrates a method from one implementation of converting a character string into a date format. When the conversion is complete, the data can be stored in a column defined as having some form of a DATETIME data type.

   
   

   SELECT TO_DATE('JANUARY 01 1998','MONTH DD YYYY')

   FROM EMPLOYEE_PAY_TBL;

   
TO_DATE('
----------
01-JAN-99
01-JAN-99
01-JAN-99
01-JAN-99
01-JAN-99
01-JAN-99

6 rows selected.

You may be wondering why six rows were selected from this query when only one date value was provided. The reason is because the conversion of the literal string was selected from the EMPLOYEE_PAY_TBL, which has six rows of data. Hence, the conversion of the literal string was selected against each record in the table.

Summary | Next Section

Previous Section