Hour 12. Understanding Dates and Times

In this hour, you learn about the nature of dates and time in SQL. Not only does this hour discuss the DATETIME data type in more detail, but you also see how some implementations use dates, how to extract the date and time in a desired format, and some of the common rules.

How Is a Date Stored?

Each implementation has a default storage format for the date and time. This default storage often varies among different implementations, as do other data types for each implementation. The following sections begin by reviewing the standard format of the DATETIME data type and its elements. Then you see the data types for date and time in some popular implementations of SQL, including Oracle, MySQL, and Microsoft SQL Server.

Standard Data Types for Date and Time

There are three standard SQL data types for date and time (DATETIME) storage:

• DATE— Stores date literals. DATE is formatted as YYYY-MM-DD and ranges from 0001-01-01 to 9999-12-31.

• TIME— Stores time literals. TIME is formatted as HH:MI:SS.nn... and ranges from 00:00:00... to 23:59:61.999....

• TIMESTAMP— Stores date and time literals. TIMESTAMP is formatted as YYYY-MM-DD HH:MI:SS.nn... and ranges from 0001-01-01 00:00:00... to 9999-12-31 23:59:61.999....

DATETIME Elements

DATETIME elements are those elements pertaining to date and time that are included as part of a DATETIME definition. The following is a list of the constrained DATETIME elements and a valid range of values for each element:

Each of these elements is an element of time that we deal with on a daily basis. Seconds can be represented as a decimal, allowing the expression of tenths of a second, hundredths of a second, milliseconds, and so on. You might question the fact that a minute can contain more than 60 seconds. According to the ANSI standard, this 61.999 seconds is due to the possible insertion or omission of a leap second in a minute, which in itself is a rare occurrence. Refer to your implementation on the allowed values because date and time storage might vary widely.

Implementation-Specific Data Types

As with other data types, each implementation provides its own representation and syntax. Table 12.1 shows how three products (Microsoft SQL Server, MySQL, and Oracle) have been implemented with date and time.

Table 12.1. DATETIME Types Across Platforms

Date Functions

Date functions are available in SQL depending on the options with each specific implementation. Date functions, similar to character string functions, are used to manipulate the representation of date and time data. Available date functions are often used to format the output of dates and time in an appealing format, compare date values with one another, compute intervals between dates, and so on.

The Current Date

You might have already raised the question, “How do I get the current date from the database?” The need to retrieve the current date from the database might originate from several situations, but the current date is normally returned either to compare it to a stored date or to return the value of the current date as some sort of timestamp.

The current date is ultimately stored on the host computer for the database and is called the system date. The database, which interfaces with the appropriate operating system, has the capability to retrieve the system date for its own purpose or to resolve database requests, such as queries.

Take a look at a couple of methods of attaining the system date based on commands from two different implementations.

Microsoft SQL Server uses a function called GETDATE() to return the system date. This function is used in a query as follows. The output is what would return if today’s current date were New Year’s Eve for 2010.

SELECT GETDATE()

Dec 31, 2010

MySQL uses the NOW function to retrieve the current date and time. NOW is called a pseudocolumn because it acts as any other column in a table and can be selected from any table in the database although it is not actually part of the table’s definition.

The following MySQL statement returns the output if today were New Year’s Eve before 2011:

SELECT NOW ();

31-DEC-11 13:41:45

Oracle uses a function known as SYSDATE and looks like this if using the DUAL table, which is a dummy table in Oracle:

SELECT SYSDATE FROM DUAL;

31-DEC-11 13:41:45

Time Zones

The use of time zones might be a factor when dealing with date and time information. For instance, a time of 6:00 p.m. in the central United States does not equate to the same time in Australia, although the actual point in time is the same. Some of us who live within the daylight saving time zone are used to adjusting our clocks twice a year. If time zones are considerations when maintaining data in your case, you might find it necessary to consider time zones and perform time conversions, if available with your SQL implementation.

The following are some common time zones and their abbreviations.

The following table shows examples of time zone differences based on a given time.

Adding Time to Dates

Days, months, and other parts of time can be added to dates for the purpose of comparing dates to one another or to provide more specific conditions in the WHERE clause of a query.

Intervals can be used to add periods of time to a DATETIME value. As defined by the standard, intervals can manipulate the value of a DATETIME value, as in the following examples:

DATE '2010-12-31' + INTERVAL '1' DAY

'2011-01-01'

DATE '2010-12-31' + INTERVAL '1' MONTH

'2011-01-31'

The following is an example using the SQL Server function DATEADD:

Click here to view code image

SELECT DATE_HIRE, DATEADD(MONTH, 1, DATE_HIRE)
FROM EMPLOYEE_PAY_TBL;

DATE_HIRE ADD_MONTH
--------- ---------
23-MAY-99 23-JUN-99
17-JUN-00 17-JUL-00
14-AUG-04 14-SEP-04
28-JUN-07 28-JUL-07
22-JUL-06 22-AUG-06
14-JAN-01 14-FEB-01

6 rows affected.

The following example uses the Oracle function ADD_MONTHS:

Click here to view code image

SELECT DATE_HIRE, ADD_MONTHS(DATE_HIRE,1)
FROM EMPLOYEE_PAY_TBL;

DATE_HIRE             ADD_MONTH
---------             ---------
23-MAY-99             23-JUN-99

17-JUN-00             17-JUL-00
14-AUG-04             14-SEP-04
28-JUN-07             28-JUL-07
22-JUL-06             22-AUG-06
14-JAN-01             14-FEB-01

6 rows selected.

To add one day to a date in Oracle, use the following:

SELECT DATE_HIRE, DATE_HIRE + 1
FROM EMPLOYEE_PAY_TBL
WHERE EMP_ID = '311549902';

DATE_HIRE       DATE_HIRE
---------       ---------
23-MAY-99       24-MAY-99

1 row selected.

If you wanted to do the same query in MySQL, you would use the ANSI standard INTERVAL command, as follows. Otherwise, MySQL would convert the date to an integer and try to perform the operation.

Click here to view code image

SELECT DATE_HIRE, DATE_ADD(DATE_HIRE, INTERVAL 1 DAY), DATE_HIRE + 1
FROM EMPLOYEE_PAY_TBL
WHERE EMP_ID = '311549902';

DATE_HIRE              DATE_ADD              DATE_HIRE+1
---------              ---------------       ----------------
23-MAY-99              24-MAY-99             1990524

1 row selected.

Notice that these examples in MySQL, SQL Server, and Oracle, although they differ syntactically from the ANSI examples, derive their results based on the same concept as described by the SQL standard.

Miscellaneous Date Functions

Table 12.2 shows some powerful date functions that exist in the implementations for SQL Server, Oracle, and MySQL.

Table 12.2. Date Functions by Platform

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 converts data types into other data types. The basic syntax is as follows:

CAST ( EXPRESSION AS NEW_DATA_TYPE )

Specific syntax examples 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 might 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

2010-12-31
31-DEC-10
2010-12-31 23:59:01.11
...

What if you want the date to be displayed as the following?

December 31, 2010

You would have to convert the date from a DATETIME format into a character string format. This is accomplished by implementation-specific functions for this very purpose, further illustrated in the following sections.

Table 12.3 displays some of the common date parts used in various implementations. This aids you in using the date picture in the following sections to extract the proper DATETIME information from the database.

Table 12.3.

Converting Dates to Character Strings

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

SELECT DATE_HIRE = DATENAME(MONTH, DATE_HIRE)
FROM EMPLOYEE_PAY_TBL;

DATE_HIRE
---------
May
June
August
June
July
January

6 rows affected.

The second example is an Oracle date conversion using the TO_CHAR function:

Click here to view code image

SELECT DATE_HIRE, TO_CHAR(DATE_HIRE,'Month dd, yyyy') HIRE
FROM EMPLOYEE_PAY_TBL;

DATE_HIRE             HIRE
----------            ---------------

23-MAY-99             May       23, 1999
17-JUN-00             June      17, 2000
14-AUG-04             August    14, 2004
28-JUN-07             June      28, 2007
22-JUL-06             July      22, 2006
14-JAN-01             January   14, 2001

6 rows selected.

Converting Character Strings to Dates

The following example illustrates a method from a MySQL or Oracle 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.

Click here to view code image

SELECT STR_TO_DATE('01/01/2010 12:00:00 AM', '%m/%d/%Y %h:%i:%s %p') AS FORMAT_DATE
FROM EMPLOYEE_PAY_TBL;

FORMAT_DATE
-----------
01-JAN-10
01-JAN-10
01-JAN-10
01-JAN-10
01-JAN-10
01-JAN-10

6 rows selected.

You might be wondering why six rows were selected from this query when only one date value was provided. It’s 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.

In Microsoft SQL Server we instead use the CONVERT function:

Click here to view code image

SELECT CONVERT(DATETIME,'02/25/2010 12:00:00 AM') AS FORMAT_DATE
FROM EMPLOYEE_PAY_TBL;
FORMAT_DATE
-----------------------
2010-02-25 00:00:00.000
2010-02-25 00:00:00.000

2010-02-25 00:00:00.000
2010-02-25 00:00:00.000
2010-02-25 00:00:00.000
2010-02-25 00:00:00.000

6 rows selected.

Summary

You have an understanding of DATETIME values based on the fact that ANSI has provided a standard. However, as with many SQL elements, most implementations have deviated from the exact functions and syntax of standard SQL commands, although the concepts remain the same as far as the basic representation and manipulation of date and time information. In Hour 11, “Restructuring the Appearance of Data,” you saw how functions varied depending on each implementation. This hour, you have seen some of the differences between date and time data types, functions, and operators. Keep in mind that not all examples discussed in this hour work with your particular implementation, but the concepts of dates and times are the same and should be applicable to any implementation.

Q&A

Q. Why do implementations choose to deviate from a single standard set of data types and functions?

A. Implementations differ as far as the representation of data types and functions mainly because of the way each vendor has chosen to internally store data and provide the most efficient means of data retrieval. However, all implementations should provide the same means for the storage of date and time values based on the required elements prescribed by ANSI, such as the year, month, day, hour, minute, second, and so on.

Q. What if I want to store date and time information differently than what is available in my implementation?

A. Dates can be stored in nearly any type of format if you choose to define the column for a date as a variable length character. The main thing to remember is that when comparing date values to one another, you are usually required to first convert the character string representation of the date to a valid DATETIME format for your implementation—that is, if appropriate conversion functions are available.

Workshop

The following workshop is composed of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises are intended to afford you the opportunity to apply the concepts discussed during the current hour, as well as build upon the knowledge acquired in previous hours of study. Please take time to complete the quiz questions and exercises before continuing. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.

Quiz

1. From where is the system date and time normally derived?

2. What are the standard internal elements of a DATETIME value?

3. What could be a major factor concerning the representation and comparison of date and time values if your company is an international organization?

4. Can a character string date value be compared to a date value defined as a valid DATETIME data type?

5. What would you use in SQL Server, MySQL, and Oracle to get the current date and time?

Exercises

1. Type the following SQL code into the sql prompt in each of the implementations to display the current date from the database:

a. MySQL : SELECT CURRENT_DATE;

b. SQL Server : SELECT GETDATE();

c. Oracle : SELECT SYSDATE FROM DUAL;

2. Type the following SQL code to display each employee’s hire date:

SELECT EMP_ID, DATE_HIRE
FROM EMPLOYEE_PAY_TBL;

3. In MySQL, dates can be displayed in various formats using the EXTRACT function in conjunction with the MySQL date pictures. Type the following code to display the year that each employee was hired:

SELECT EMP_ID, EXTRACT(YEAR FROM DATE_HIRE)
FROM EMPLOYEE_PAY_TBL;

4. Try the following similar syntax in Microsoft SQL Server:

SELECT EMP_ID, YEAR( DATE_HIRE)
FROM EMPLOYEE_PAY_TBL;

5. Type in a statement similar to this MySQL implementation to display each of the employees’ hire dates along with today’s date:

SELECT EMP_ID, DATE_HIRE, CURRENT_DATE
FROM EMPLOYEE_PAY_TBL;

6. On what day of the week was each employee hired?

7. What is today’s Julian date (day of year)?

8. Type in three SQL statements. The first to get the current system DATETIME as you did in Exercise 1, the second to convert the system DATETIME to a date value, and the third to convert the system DATETIME to a pure time value.