What You’ll Learn in This Hour:
▶ Understanding how date and time are stored
▶ Understanding typical date and time formats
▶ Using date functions
▶ Using date conversions
In this hour, you learn about the nature of dates and times in SQL. Not only does this hour discuss the DATETIME data type in more detail, but you also see how some implementations use dates, learn how to extract the date and time in your desired format, and familiarize yourself with some of the common rules.
Note
Variations in the SQL Syntax
As you know by now, many different SQL implementations exist. This book follows the American National Standards Institute (ANSI) standard and illustrates the most common nonstandard functions, commands, and operators. Oracle is used for the examples. Even in Oracle, however, the date can be stored in different formats, so check your particular implementation for details. No matter how it is stored, your implementation should have functions that convert date formats.
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.
Three standard SQL data types are used for date and time (DATETIME) storage. In a relational database, the date can be stored and retrieved as a basic date format, or can include a time of day, or can include a time stamp based on certain intervals (according to the options available in each SQL implementation):
▶ 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 ElementsDATETIME elements pertain to the date and time and 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.
DATETIME Element |
Valid Ranges |
|
|
|
|
|
|
|
|
|
|
|
|
Each of these is an element of time that you deal with on a daily basis. Seconds can be represented as a decimal, enabling you to express tenths of a second, hundredths of a second, milliseconds, and so on. According to the ANSI standard, a minute is defined as 61.999 seconds, due to the possible insertion or omission of a leap second in a minute, which is a rare occurrence. Refer to your implementation on the allowed values; date and time storage can vary widely.
Tip
Databases Handle Leap Years
Date variances, such as leap seconds and leap years, are handled internally by the database if the data is stored in a DATETIME data type.
As with other data types, each implementation provides its own representation and syntax. Table 16.1 shows how three products (Microsoft SQL Server, MySQL, and Oracle) have been implemented with a date and time.
TABLE 16.1 DATETIME Types Across Platforms
Product |
Data Type |
Use |
Oracle |
|
Stores both date and time information |
SQL Server |
|
Stores both date and time information |
|
Same as |
|
|
Stores a date value |
|
|
Stores a time value |
|
MySQL |
|
Stores both date and time information |
|
Stores both date and time information |
|
|
Stores a date value |
|
|
Stores a time value |
|
|
1-byte type that represents the year |
Tip
Even Date and Time Types Can Differ
Each implementation has its own specific data type(s) for date and time information. However, most implementations comply with the ANSI standard; all elements of the date and time are included in their associated data types. The way the date is internally stored is implementation dependent.
Date functions are available in SQL, depending on the options with each specific implementation. Date functions, similar to character string functions, 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 is normally returned either to compare it to a stored date or to return the value of the current date as some sort of time stamp. 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 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:
SELECT GETDATE() 2015-06-01 19:23:38.167
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; however, it is not actually part of the table’s definition.
The following MySQL statement returns the output if today were June 1, 2015:
SELECT NOW (); 01-JUN-15 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; 01-JUN-15 13:41:45
The default date format is typically set by the database administrator upon database creation. Oracle’s default date normally includes a 2-digit year. The following command used at the Oracle SQL prompt changes the date format for the current session for the sample database so that it includes the full 4-digit year, to avoid any confusion.
SQL> alter session set NLS_DATE_FORMAT='mm-dd-yyyy'; Session altered.
Because no date columns are stored in the current BIRDS database, the following PHOTOGRAPHERS table was created to include the photographer’s date of birth and the date that a given photographer started photography. In Oracle, the datatype for a date is simply DATE. The PHOTOGRAPHERS table also has records inserted to include the two new date fields.
SQL> drop table photographers; Table dropped. SQL> create table photographers 2 (p_id number(3) not null primary key, 3 photographer varchar(30) not null, 4 mentor_p_id number(3) null, 5 dob date not null, 6 dt_start_photo date not null, 7 constraint p_fk1 foreign key (mentor_p_id) references photographers (p_id)); Table created. SQL> insert into photographers values ( 7, 'Ryan Notstephens' , null, '07-16-1975', '07-16-1989'); 1 row created. SQL> insert into photographers values ( 8, 'Susan Willamson' , null, '12-03-1979', '02-22-2016'); 1 row created. SQL> insert into photographers values ( 9, 'Mark Fife' , null, '01-31-1982', '12-25-2000'); 1 row created. SQL> insert into photographers values ( 1, 'Shooter McGavin' , null, '02-24-2005', '01-01-2019'); 1 row created. SQL> insert into photographers values ( 2, 'Jenny Forest' , 8, '08-15-1963', '08-16-1983'); 1 row created. SQL> insert into photographers values ( 3, 'Steve Hamm' , null, '09-14-1969', '01-01-2000'); 1 row created. SQL> insert into photographers values ( 4, 'Harry Henderson' , 9, '03-22-1985', '05-16-2011'); 1 row created. SQL> insert into photographers values ( 5, 'Kelly Hairtrigger' , 8, '01-25-2001', '02-01-2019'); 1 row created. SQL> insert into photographers values ( 6, 'Gordon Flash' , null, '09-14-1971', '10-10-2010'); 1 row created. SQL> insert into photographers values ( 10, 'Kate Kapteur' , 7, '09-14-1969', '11-07-1976'); 1 row created. SQL> commit; Commit complete.
The current date is ultimately stored on the host computer for the database and is called the system date. The following query returns the system date from the DUAL table. As mentioned previously, this is a dummy table in Oracle from which any literal string can be submitted.
SQL> select sysdate from dual; SYSDATE ---------- 07-24-2021 1 row selected.
In the next example, you select the system date from every row of data in the PHOTOGRAPHERS table. This might not seem desirable, but it offers an illustration of how the system date can be compared to any value that is stored in a database column, just like any literal string.
SQL> select sysdate from photographers; SYSDATE ---------- 07-24-2021 07-24-2021 07-24-2021 07-24-2021 07-24-2021 07-24-2021 07-24-2021 07-24-2021 07-24-2021 07-24-2021 10 rows selected.
In practical use, you might use the system date to compare photographers’ dates of birth or dates photographers started taking photos as a hobby or career.
Time zones can 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. People who live within the daylight savings time zone are accustomed to adjusting their clocks twice a year. If time zones are considerations when maintaining data in your case, you might need to consider time zones and perform time conversions, if they are available with your SQL implementation.
The following table lists some common time zones and their abbreviations.
Abbreviation |
Time Zone |
AST, ADT |
Atlantic Standard Time, Atlantic Daylight Time |
BST, BDT |
Bering Standard Time, Bering Daylight Time |
CST, CDT |
Central Standard Time, Central Daylight Time |
EST, EDT |
Eastern Standard Time, Eastern Daylight Time |
GMT |
Greenwich Mean Time |
HST, HDT |
Alaska/Hawaii Standard Time, Alaska/Hawaii Daylight Time |
MST, MDT |
Mountain Standard Time, Mountain Daylight Time |
NST |
Newfoundland Standard Time, Newfoundland Daylight Time |
PST, PDT |
Pacific Standard Time, Pacific Daylight Time |
YST, YDT |
Yukon Standard Time, Yukon Daylight Time |
The following table shows examples of time zone differences based on a given time.
Time Zone |
Time |
AST |
June 12, 2015, at 1:15 p.m. |
BST |
June 12, 2015, at 6:15 a.m. |
CST |
June 12, 2015, at 11:15 a.m. |
EST |
June 12, 2015, at 12:15 p.m. |
GMT |
June 12, 2015, at 5:15 p.m. |
HST |
June 12, 2015, at 7:15 a.m. |
MST |
June 12, 2015, at 10:15 a.m. |
NST |
June 12, 2015, at 1:45 p.m. |
PST |
June 12, 2015, at 9:15 a.m. |
YST |
June 12, 2015, at 8:15 a.m. |
Note
Handling Time Zones
Some implementations have functions that enable you to deal with different time zones. However, not all implementations support the use of time zones. Be sure to verify the use of time zones in your particular implementation, as well as your database’s need to deal with them.
You can add days, months, and other components of time to dates so that you can compare them or provide more specific conditions in the WHERE clause of a query.
The following example is a query from the PHOTOGRAPHERS table that shows the photographer’s name and date of birth.
SQL> select photographer, dob 2 from photographers; PHOTOGRAPHER DOB ------------------------------ ---------- Ryan Notstephens 07-16-1975 Susan Willamson 12-03-1979 Mark Fife 01-31-1982 Shooter McGavin 02-24-2005 Jenny Forest 08-15-1963 Steve Hamm 09-14-1969 Harry Henderson 03-22-1985 Kelly Hairtrigger 01-25-2001 Gordon Flash 09-14-1971 Kate Kapteur 09-14-1969 10 rows selected.
Now that you have a visual of the data stored in the PHOTOGRAPHERS table for the date of birth, you can perform the following query, which uses the Oracle ADD_MONTH function to add 12 months, or 1 year, to the photographer’s date of birth. Remember that every implementation of SQL can have functions that vary in syntax, but conceptually, they are all similar as related to the SQL standard.
SQL> select photographer, dob, add_months(dob, 12) "FIRST BIRTHDAY" 2 from photographers; PHOTOGRAPHER DOB FIRST BIRT ------------------------------ ---------- ---------- Ryan Notstephens 07-16-1975 07-16-1976 Susan Willamson 12-03-1979 12-03-1980 Mark Fife 01-31-1982 01-31-1983 Shooter McGavin 02-24-2005 02-24-2006 Jenny Forest 08-15-1963 08-15-1964 Steve Hamm 09-14-1969 09-14-1970 Harry Henderson 03-22-1985 03-22-1986 Kelly Hairtrigger 01-25-2001 01-25-2002 Gordon Flash 09-14-1971 09-14-1972 Kate Kapteur 09-14-1969 09-14-1970 10 rows selected.
The following query is essentially the same as a previous query, except that you add 365 days instead of 12 months to the date of birth. Compare the results to the previous query.
SQL> select photographer, dob, dob + 365 "FIRST BIRTHDAY" 2 from photographers; PHOTOGRAPHER DOB FIRST BIRT ------------------------------ ---------- ---------- Ryan Notstephens 07-16-1975 07-15-1976 Susan Willamson 12-03-1979 12-02-1980 Mark Fife 01-31-1982 01-31-1983 Shooter McGavin 02-24-2005 02-24-2006 Jenny Forest 08-15-1963 08-14-1964 Steve Hamm 09-14-1969 09-14-1970 Harry Henderson 03-22-1985 03-22-1986 Kelly Hairtrigger 01-25-2001 01-25-2002 Gordon Flash 09-14-1971 09-13-1972 Kate Kapteur 09-14-1969 09-14-1970 10 rows selected.
Note
Variations Between Implementations
Remember that the date, formatting, and functions can vary among implementations. Although different RDBMS implementations might differ syntactically from the exact ANSI standard, they derive their results based on the same concept described by the SQL standard.
Not only can you add time to a date, but you also can subtract time and incorporate other arithmetic into your query using date values.
The following example selects the photographer’s name and the date of birth, but it subtracts the date of birth from the system date (which is today’s date) and divides the entire calculation by 365 days per year. This simple calculation derives the age of each photographer.
SQL> select photographer, (sysdate - dob)/365 "AGE" 2 from photographers; PHOTOGRAPHER AGE ------------------------------ ---------- Ryan Notstephens 46.0561876 Susan Willamson 41.6698863 Mark Fife 39.5055027 Shooter McGavin 16.4233109 Jenny Forest 57.982215 Steve Hamm 51.8945438 Harry Henderson 36.3657767 Kelly Hairtrigger 20.5082424 Gordon Flash 49.8945438 Kate Kapteur 51.8945438 10 rows selected.
In the previous query, notice that each photographer’s age is expressed as a decimal. Of course, most people do not talk about age in decimal format. In this example, therefore, the date calculation is embedded within the ROUND function to round the age up or down accordingly. This is an example of combining arithmetic and other functions with date values to get the data that you need out of the database.
SQL> select photographer, round((sysdate - dob)/365) "AGE" 2 from photographers; PHOTOGRAPHER AGE ------------------------------ ---------- Ryan Notstephens 46 Susan Willamson 42 Mark Fife 40 Shooter McGavin 16 Jenny Forest 58 Steve Hamm 52 Harry Henderson 36 Kelly Hairtrigger 21 Gordon Flash 50 Kate Kapteur 52 10 rows selected.
In the following example, you want to create a query that shows the number of years of experience that Gordon Flash has. You thus eliminate all other records in the table using the WHERE clause, and instead of using the DOB column as in the previous examples, you subtract the date that Gordon started taking photos from the system date and divide by 365 days. This shows that Gordon Flash has approximately 11 years of experience in photography.
SQL> select photographer, round((sysdate - dt_start_photo)/365) "YEARS EXPERIENCE" 2 from photographers 3 where photographer = 'Gordon Flash'; PHOTOGRAPHER YEARS EXPERIENCE ------------------------------ ---------------- Gordon Flash 11 1 row selected.
Table 16.2 shows some powerful date functions that are available in the implementations for SQL Server, Oracle, and MySQL.
TABLE 16.2 Date Functions, by Platform
Product |
Date Function |
Use |
SQL Server |
|
Returns the integer value of a |
|
Returns the text value of a |
|
|
Returns the system date |
|
|
Returns the difference between two dates for specified date parts, such as days, minutes, and seconds |
|
Oracle |
|
Returns the next day of the week as specified (for example, |
|
Returns the number of months between two given dates |
|
MySQL |
|
Displays the day of the week |
|
Displays the day of the month |
|
|
Displays the day of the week |
|
|
Displays the day of the year |
You might need to convert dates for a variety of reasons. Conversions are mainly done 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 follows:
CAST ( EXPRESSION AS NEW_DATA_TYPE )
The following subsections illustrate specific syntax examples of some implementations and cover these tasks:
▶ Representing the parts of a DATETIME value
▶ Converting dates to character strings
▶ Converting character strings to dates
A date picture consists 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 the following:
2010-12-31 31-DEC-10 2010-12-31 23:59:01.11 ...
Let’s say you want the date to display like this:
December 31, 2010
You now have to convert the date from a DATETIME format into a character string format. You do so using implementation-specific functions for this purpose, as you see in the following sections.
Table 16.3 displays some of the common date parts used in various implementations. This helps you use the date picture in the following sections to extract the proper DATETIME information from the database.
TABLE 16.3 Date Parts, by SQL Implementation
Product |
Syntax |
Date Part |
SQL Server |
|
Year |
|
Quarter |
|
|
Month |
|
|
Day of the year |
|
|
Week |
|
|
Weekday |
|
|
Hour |
|
|
Minute |
|
|
Second |
|
|
Millisecond |
|
Oracle |
|
Anno Domini |
|
Ante meridian |
|
|
Before Christ |
|
|
Century |
|
|
Number of the day in the week |
|
|
Number of the day in the month |
|
|
Number of the day in the year |
|
|
The day spelled out ( |
|
|
The day spelled out ( |
|
|
The day spelled out ( |
|
|
The three-letter abbreviation of the day ( |
|
|
The three-letter abbreviation of the day (Mon) |
|
|
The three-letter abbreviation of the day (mon) |
|
|
Hour of the day |
|
|
Hour of the day |
|
|
Hour of the day for a 24-hour clock |
|
|
Julian days since 12-31-4713 B.C. |
|
|
Minute of the hour |
|
|
The number of the month |
|
|
The three-letter abbreviation of the month (JAN) |
|
|
The three-letter abbreviation of the month (Jan) |
|
|
The three-letter abbreviation of the month (jan) |
|
|
The month spelled out (JANUARY) |
|
|
The month spelled out (January) |
|
|
The month spelled out (january) |
|
|
Post meridian |
|
|
The number of the quarter |
|
|
The Roman numeral for the month |
|
|
The two digits of the year |
|
|
The second of a minute |
|
|
The seconds since midnight |
|
|
The signed year; if B.C. 500, B.C. = –500 |
|
|
The number of the week in a month |
|
Oracle |
|
The number of the week in a year |
|
The last digit of the year |
|
|
The last two digits of the year |
|
|
The last three digits of the year |
|
|
The year |
|
|
The year spelled out (TWO-THOUSAND-TEN) |
|
|
The year spelled out (Two-Thousand-Ten) |
|
|
The year spelled out (two-thousand-ten) |
|
MySQL |
|
Seconds |
|
Minutes |
|
|
Hours |
|
|
Days |
|
|
Months |
|
|
Years |
|
|
Minutes and seconds |
|
|
Hours and minutes |
|
|
Days and hours |
|
|
Years and months |
|
|
Hours, minutes, and seconds |
|
|
Days and minutes |
|
|
Days and seconds |
Note
Date Parts in Oracle
This section uses some of the most common date parts for Oracle. Other date parts might be available, depending on the version of Oracle.
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.
SQL implementations provide a variety of formats for displaying a date value. In this next example, you select DOB for photographers and also perform a character conversion on the default date format that is stored in the database, to produce output that might be more readable or desirable for a given situation. Take a minute to study the results.
SQL> select photographer, dob, to_char(dob, 'Day Month dd, yyyy') "LONG DOB" 2 from photographers; PHOTOGRAPHER DOB LONG DOB --------------------- ---------- ------------------------------ Ryan Notstephens 07-16-1975 Wednesday July 16, 1975 Susan Willamson 12-03-1979 Monday December 03, 1979 Mark Fife 01-31-1982 Sunday January 31, 1982 Shooter McGavin 02-24-2005 Thursday February 24, 2005 Jenny Forest 08-15-1963 Thursday August 15, 1963 Steve Hamm 09-14-1969 Sunday September 14, 1969 Harry Henderson 03-22-1985 Friday March 22, 1985 Kelly Hairtrigger 01-25-2001 Thursday January 25, 2001 Gordon Flash 09-14-1971 Tuesday September 14, 1971 Kate Kapteur 09-14-1969 Sunday September 14, 1969 10 rows selected.
You can also convert character strings to dates. The following query shows an example of converting a character string using the TO_DATE function into a date value based on a literal string that you provide in the query. You can see in the results that an error was returned because of a built-in constraint within the database. The day of the week that you provided conflicts with the actual day of the week for that date in time, which the database “knows.”
SQL> select to_date('Tuesday January 6, 1999',
2 'Day Month dd, yyyy') "New Date"
3 from dual;
select to_date('Tuesday January 6, 1999',
*
ERROR at line 1:
ORA-01835: day of week conflicts with Julian date
In this final example, the two DATE functions successfully convert a literal string into a date value.
SQL> select to_date('Sunday September 14, 1969',
2 Day Month dd, yyyy') "New Date"
3 from dual;
New Date
----------
09-14-1969
1 row selected.
You now should have an understanding of DATETIME values. ANSI provided a standard; however, as with many SQL elements, most implementations have deviated from the exact functions and syntax of standard SQL commands. Still, the concepts remain the same in terms of the basic representation and manipulation of date and time information. In Hour 15, “Restructuring the Appearance of Data,” you saw that functions vary depending on each implementation. This hour, you saw 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. Why do implementations choose to deviate from a single standard set of data types and functions?
A. Implementations differ in their representation of data types and functions mainly because of the way each vendor chooses to internally store data and provide the most efficient means of data retrieval. However, all implementations should provide the same means for storing 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 my implementation offers?
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 point to remember when comparing date values to one another is that 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.
The following workshop consists 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 give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
1. Where are the system date and time normally derived from?
2. What are the standard internal elements of a DATETIME value?
3. What is a major factor for international organizations when representing and comparing date and time values?
4. Can a character string date value be compared to a date value defined as a valid DATETIME data type?
5. What do you use in SQL Server and Oracle to get the current date and time?
1. Type the following SQL code into the sql prompt to display the current date from the database:
SELECT SYSDATE FROM DUAL;
2. Create the PHOTOGRAPHERS table and insert the data shown at the beginning of this hour for these exercises.
3. Write a query to display all the data in the PHOTOGRAPHERS table that you just created.
4. Calculate your own age in a query using the system date.
5. Display the day of the week only that each photographer was born.
6. What is the age of Harry Henderson (rounded, of course, unless you just need to know)?
7. Which photographer has been taking photos the longest?
8. Were any photographers born on the same day?
9. Which photographers might have started taking photos because of a New Year’s resolution?
10. Write a query to determine today’s Julian date (day of year).
11. What is the combined age of all the photographers in the database?
12. Which photographer started taking photos at the youngest age?
13. Have some fun and come up with some queries of your own on this database or simply using the system date.