Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

DATE and TIME ... don't be scared, and do it right (the first time)

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Published:
Updated:
DATE and TIME ... don't be scared, just do it right

DATE,TIME,MS SQL Server,MySQL,Oracle


0. Introduction


I presume you all have already worked with databases and queries, and found some difficulties to get the date/time stuff right. Though it's not rocket science, the purpose here is to teach you how to handle date and times, and to avoid implicit data type conversions.
NOTE: I refer to the versions MSSQL Server 7 through 2008, MySQL 4.x through 5.x and higher, and Oracle 8.x through 11.x, unless specified otherwise. Most of the information is version independent, though.

1. The data type names


Be sure to know the data type when you declare your variables and table columns!

In MS SQL Server, the data type is named DATETIME, containing both date and time.
 => SQL Server 2008 comes with a range of new data types for the date and time, see also Step 2
MySQL has DATE, DATETIME and TIMESTAMP (datetime + offset).
Oracle has DATE and TIMESTAMP (datetime + offset).

Don't get fooled by MS SQL Server: it does have a data type called TIMESTAMP, but it is NOT the same as Oracle's timestamp, and actually, it is not a date or time at all. In short: remember to forget that data type in SQL server: http://msdn.microsoft.com/en-us/library/ms182776.aspx

2. The data type limitations


You have to know the limitations of the data type in the relevant database. For that, read up on the scope and limitations of them for your database.
SQL Server: http://msdn.microsoft.com/en-us/library/ms187752.aspx
MySQL 5.1: http://dev.mysql.com/doc/refman/5.1/en/datetime.html
Oracle: http://www.techonthenet.com/oracle/datatypes.php

The key limitation is the supported range of values:

in MS SQL Server:
DATETIME: '1753-01-01 00:00:00.000' to '9999-12-31 23:59:59.997'
new since SQL 2008:
DATETIME2, DATETIMEOFFSET : '0001-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

in MySQL:
DATE and DATETIME: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP: '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'  

in Oracle:
DATE and TIMESTAMP: '4712BC-01-01 00:00:00' to '9999-12-31 23:59:59'

Another limitation is the precision for the time:
in MSSQL Server:
DATETIME: 3 milliseconds
DATETIME2 and TIME: 100 nanoseconds

Ensure you pick the right data type for your database and date fields!

We won't discuss the TIMESTAMP type here; that is worth an article on its own.

3. Know the functions/operations


In every database, you have very useful and basic functions to work with date and time. Know them, and use them.

MS SQL Server: http://msdn.microsoft.com/en-us/library/ms186724.aspx
MySQL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Oracle: http://psoug.org/reference/date_func.html

You will notice that each database product has its own palette of tools, and unfortunately, almost all of them are named (slightly) differently.

4. DATA vs ENTRY/FORMAT


The BIGGEST mistake of a newcomer to databases, when working with date and time, is to "ignore" the difference between how the data is stored, as opposed to how it is displayed. This is most often due to the fact that all database products have a certain level of tolerance for the "input" of data.

To put it bluntly: it is surely interesting how, exactly, the data is stored in the database bit-wise, but it is irrelevant to the how to code with dates and times.

What follows is a first code example of what is GOOD vs BAD. First, we create a table for the examples (in our case, Microsoft SQL Server 2005 Express Edition):
CREATE TABLE orders 
                      ( order_id int identity
                      , order_date datetime
                      , ordering_client_id int 
                      )
                      > Command(s) completed successfully.

Open in new window

Then we insert an "order" for today. No problem:
INSERT INTO orders ( order_date , ordering_client_id ) 
                      VALUES ( getdate(), 1 )
                      > (1 row(s) affected)

Open in new window


Then we insert an order for 31 August 2009, using the US mm/dd/yyyy format:
INSERT INTO orders ( order_date , ordering_client_id ) 
                      VALUES ( '08/31/2009', 1 )
                      >Msg 242, Level 16, State 3, Line 1
                      >The conversion of a char data type to a datetime data type resulted 
                      >in an out-of-range datetime value.
                      >The statement has been terminated.

Open in new window

That code CAN work, as long as you connected with a login that has a language that says "us-english" (see step 7). Otherwise, you will probably run into the error shown.

Why is that so? In layman's terms: You have asked the database engine to take this -- '08/31/2009' -- and put it into a datetime field. But '08/31/2009' is not a date; it is a string. Yes, any human reader will tell you that is shows a date, but still it is a string, as the syntax tells: single quotes delimiter => string (in database terminology, it is called a varchar).

Try to guess how a database engine would interpret this date as: '01/02/03'
* 1 Feb 2003 ?
* 2 Jan 2003 ?
* 3 Feb 2001 ?

Enough playing; let's see "how to do it the right way".

in MS SQL Server, use the CONVERT function:
INSERT INTO orders ( order_date , ordering_client_id ) 
                      VALUES ( CONVERT(DATETIME, '08/31/2009', 101 ), 1 )

Open in new window

What's that 101 in there? See here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

In MySQL, use STR_TO_DATE: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date
INSERT INTO orders ( order_date , ordering_client_id ) 
                      VALUES ( STR_TO_DATE( '08/31/2009', '%m/%d/%Y' ), 1 )

Open in new window


in Oracle, use TO_DATE: http://www.techonthenet.com/oracle/functions/to_date.php
INSERT INTO orders ( order_date , ordering_client_id ) 
                      VALUES ( TO_DATE( '08/31/2009', 'MM/DD/YYYY' ), 1 )

Open in new window

Using the syntax above, you will NEVER get any type conversion errors, assuming the input data IS a valid date. You are respecting the date limitations and the format specified.

As you surely notice, the CONVERT() function in MS SQL Server is quite limited for the style values/formats supported. If your "format" is not in there, you will have to first use some string manipulations to get the data "right".

5. DATA vs DISPLAY/FORMAT


To display the data, you could simply issue this:
SELECT * FROM orders

Open in new window

See Step 7 on what setting will influence how the date will be displayed (aka which format).

If you run this in some programming or reporting that knows and handles or formats the date/time as needed in the front-end, you won't have any problems. However, if you are using the SQL (view/procedure/function...) to control the format (for example, for exporting or emailing or whatever) you MUST control it yourself in the SQL.

So, let's do it explicitly:
MS SQL Server (same as above: http://msdn.microsoft.com/en-us/library/ms187928.aspx)
SELECT order_date
                      , CONVERT(varchar(20), order_date, 101) us_style
                      , CONVERT(varchar(20), order_date, 103) uk_style 
                      FROM orders

Open in new window


MySQL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format
SELECT order_date
                      , DATE_FORMAT(order_date, '%m/%d/%Y') us_style
                      , DATE_FORMAT(order_date, '%d/%m/%Y') uk_style 
                      FROM orders

Open in new window


Oracle: http://www.techonthenet.com/oracle/functions/to_char.php
SELECT order_date
                      , TO_CHAR(order_date, 'MM/DD/YYYY') us_style
                      , TO_CHAR(order_date, 'DD/MM/YYYY') uk_style 
                      FROM orders

Open in new window

6. date ranges


Do you want to get the orders from yesterday? Let's see how to do that. Remember the links above for the functions? They will be useful again!

MS SQL Server:
SELECT * FROM orders
                      WHERE order_date >= DATEADD(day, -1, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))
                        AND order_date < CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)

Open in new window


MySQL:  
SELECT order_date FROM orders
                      WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
                        AND order_date < CURDATE()

Open in new window


Oracle:
SELECT order_date FROM orders
                      WHERE order_date >= TRUNC(SYSDATE)-1
                        AND order_date < TRUNC(SYSDATE)

Open in new window


As you can see, the code for MySQL is easy to read. The functions sysdate (Oracle) and getdate (SQL Server) return both date and time (despite the name). While Oracle's trunc() function will just set the time of the date parameter to '00:00:00', in MS SQL Server we need some expression like the one shown. There are other methods, like DATEADD(DAY, DATEDIFF(DAY, 0, getdate()), 0), for example.

You COULD also write like this to get the same results:
SELECT * FROM orders
                      WHERE DATEDIFF(day, order_date, getdate()) = 0

Open in new window

BUT DO NEVER DO IT LIKE THAT. If you have an index on the order_date field, it couldn't be used!

Similarly, for orders for last year, you could be tempted to write:
SELECT * FROM orders
                      WHERE DATEPART(year, order_date) = DATEPART(year, getdate()) - 1

Open in new window

A full table scan shall be the result. That's not good.

7. Implicit data type converion


for SQL Server, you have only a little control:
SET DATEFORMAT : http://msdn.microsoft.com/en-us/library/ms189491.aspx
SET LANGUAGE: http://msdn.microsoft.com/en-us/library/ms174398.aspx
... defaulting to what language the login is set to.

MySQL is simple: it will only allow this format: 'YYYY-MM-DD HH:MI:SS' / 'YYYY-MM-DD'.
Any value not following that format will be silently changed to '0000-00-00 00:00:00'.

for Oracle, it is the session parameter NLS_DATE_FORMAT which controls how the implicit data type conversion is happening : http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams122.htm.

8. Language vs Month/Day names


As seen above, you have settings that control the format, so there is just one small addition to this: month and day names.

In MS SQL Server, DATENAME(month, getdate()) will return the month's name, based on the language of the login (unless you have invoked a language using SET LANGUAGE)

In MySQL, you can control the language too: http://dev.mysql.com/doc/refman/5.1/en/locale-support.html

In Oracle, the setting NLS_DATE_FORMAT specifies the language, though you can specify, with the TO_CHAR() function, the language directly:
SELECT order_date
                      , TO_CHAR(order_date, 'DAY/MON/YYYY' ) 
                      , TO_CHAR(order_date, 'DAY/MON/YYYY', 'nls_date_language = FRENCH') 
                      FROM orders

Open in new window

9. Conclusion


As promised, no rocket science. Just simple good coding, and you won't have a problem with dates or time.
29
34,323 Views
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT

Comments (20)

Commented:
Great article! Was thinking to myself the other day, a little unhappily, that I was going to have to learn date & time functions\conversions in MySQL (which is new to me), was a little depressed...
CERTIFIED EXPERT
Author of the Year 2011
Top Expert 2006

Commented:
Terrific work!
"Yes" vote above.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Great article!

One little technique I have used:  
In my databases, I often store date-only (no time component) fields as an 8-character string, in the format 'YYYYMMDD'.   Although that can't be used in date arithmetic, I do know that I can sort on that string to order a recordset by date.   It even works when only the year is known (eg., '2012').

When I do need to do date arithmetic, Ms SQL Server (at least) happily converts 'YYYYMMDD' to a real datetime value directly, without MM vs DD ambiguity and without me needing to use hard-to-remember conversion options.

   CAST( dtReported as datetime )
   CAST( '19450129' as datetime )
   CAST( '1945' as datetime )

My most common need is to display the value for human eyeballs, and a 10-character string like '2001-12-31' is easily formated and instantly recognized.
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Outstanding article.  Voted Yes.
jkaiosIT Director

Commented:
Very helpful and well documented article!  Yes!

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.