<

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

Published on
101,745 Points
28,845 Views
29 Endorsements
Last Modified:
Awarded
Editor's Choice
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
Comment
20 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
Nice reference, Angel Eyes!

Voted yes above.
0
 
LVL 21

Expert Comment

by:alainbryden
This will be useful, I'm about to make a database that uses date and times.

PS - fix this sentence '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."

I'm sure an editor will help you get it more refined.
0
 
LVL 32

Expert Comment

by:Daniel Wilson
I'll definitely be linking to this from my article on MySQL vs MS SQL Server -- and referring myself as needed.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 

Expert Comment

by:aneilg
Thanks angelIII

Cool stuff.
0
 
LVL 73

Expert Comment

by:Qlemo
MSSQL & string date constants:
I like to use the canonical format, which allows for secure entry regardless of the format setup at the moment, and does not need a convert:

INSERT INTO orders ( order_date , ordering_client_id )
VALUES ( '20090831', 1 )
0
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
Qlemo,
>and does not need a convert:
no explicit convert, but still it IS a implicit conversion from varchar to datetime.

you are 100% correct, there is no setting that will make this work "incorrectly", the format YYYYMMDD is working consistently, and this format can be useful.

my article was mainly to focus on the problem that not being aware of having implicit conversion can lead to desaster.

thanks for your + feedback

a3
0
 

Expert Comment

by:PapaStrumpf
I have a question about table scan and clustered index scan over order table:

if you add primary key on order_id,  this query:

SELECT * FROM orders
WHERE DATEPART(year, order_date) = DATEPART(year, getdate()) - 1

will result as clustered index scan, not table scan so does that mean when u you have index on table (usually every tabke have clustered index) this method wont do table scan and can be used effectivly ?
or my bottom question is what is better performance and why over those 2:

1.
Select * from order
where convert(datetime,round(convert(float,order_date)-0.5,0)) between @dateFrom and @dateTo


2.
Select * from order
where order_date >= @dateFrom and  order_date < @dateTo


I know 1. one will use functions on every row (convert, cast..) but on both cases my execution plan is the same and i know 2. choice is better but how can i debug execution plan and know first one is bad choice ?

0
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
>will result as clustered index scan, not table scan
the difference "table scan" vs "clustered index scan" is simple:
if you have a clustered index on the table, you cannot have a "table scan", as the clustered index IS the table, in short. or the other way round: if you have a table scan => you don't have a clustered index on the table (though you might have other indexes on the table)

a clustered index scan, for the rest, is the same "bad" as a full table scan: it has to read the entire table (aka clustered index), in short, it is not using the "index" part of the clustered index.

so, yes, choice 2 is better
0
 

Expert Comment

by:PapaStrumpf
angellll thank you for explanation, so basicly i can't determine wich one of those 2 is better looking at execution plan, only way is to read SQL query and find them in code (if u know what you are looking for :) ) ?

And another one, if i see clustered index scan in my other execution plans (query's) it means my query doesnt use any "index" to perform better, so for my slow query i should get index seek of some kind  i guess.
0
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
exactly
0
 
LVL 6

Expert Comment

by:Prashant Sabnekar
Hi Angellll,
It is a nice article :) but I think using GetDate() function is not good as the performance will decrease.

Is there any way to get the Date and time (HH:MM:SS) without using GetDate() ? If there is better way the please add that also in this article to make it better.

Thanks,
Prashant
0
 
LVL 61

Expert Comment

by:Kevin Cross
Why would you think performance would decrease using GetDate() if you don't know of an alternative?  For performance to decrease, there must be some alternative that has better performance, correct?

I mean you can use CURRENT_TIMESTAMP but that is equivalent to GETDATE() -- necessity if you are wanting to get the current date dynamically without having to use a literal value.  You also have the ODBC canonical functions like {fn NOW()}, {fn CURRENT_TIMESTAMP()}, {fn CURRENT_DATE()} or {fn CURRENT_TIME()} that will work in T-SQL; however, I doubt you will notice any advantages in performance, but you can feel free to explore to see what works for you.

In the end, I believe you will find, the benefits gained by getting dynamic date and time in your query outweighs the cost of the query overhead: cost of doing business!

Regards,
Kevin
0
 
LVL 10

Expert Comment

by:lof
The article is very helpful but it would be even more useful if there was a way to quickly find it? Can you add an article to a knowledge base?
0
 
LVL 143

Author Comment

by:Guy Hengel [angelIII / a3]
lof: thanks; I reported the feature request to add articles to the knowledge base, in case that was not yet in the list.
a3
0
 
LVL 61

Expert Comment

by:Kevin Cross
lof / a3:

The feature exists already from my understanding.  In fact, to double check I just added this article to my knowledge (so kudos again, a3) since I do refer users to it often by clicking on the "Save" button above.

Hope that helps.

Regards,

m-1
0
 

Expert Comment

by:crafuse
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...
0
 
LVL 38

Expert Comment

by:younghv
Terrific work!
"Yes" vote above.
0
 
LVL 49

Expert Comment

by:DanRollins
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.
0
 
LVL 66

Expert Comment

by:Jim Horn
Outstanding article.  Voted Yes.
0
 
LVL 12

Expert Comment

by:jkaios
Very helpful and well documented article!  Yes!
0

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Join & Write a Comment

With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month