Date format in SQL Server

Question

Short Question:

What's the best date format to use in SQL Server?

Long Explanation:

We're converting our database from mysql to SQL Server. In mysql we always used int(11) to avoid the daylight savings time issue, should we be doing the same in mysql (or is DATETIME good enough)?

The use of the dates vary. Most of the time they're just informative about when various things occurred (for logging purposes). Sometimes they are used to order work to be done.

We're using 2005 (sigh), but I'd love to hear about 2008 as well.

Answer 1

I think dates should always be saved as date types (with time zones, time-parts in/excluded as necessary).

See here for possibilities (assuming you're using SQL Server 2008):

http://msdn.microsoft.com/en-us/library/ms187752.aspx

Answer 2

I have always used DATETIME to store time in MSSQL Server. It makes date and time functions pretty easy - I'm not sure how much custom code would need to be written to get the same level of functionality and speed in date/time processing using an int. To combat the daylight savings time issue, you can store the date and time in UTC instead of server time. Instead of using DateTime.Now or getDate() you can use DateTime.UtcNow and getutcdate().

Answer 3

I agree with Dave K, all dates should be stored as date datatypes.

That being said, is there a specific reason for storing dates as some other type? If you're going to go through the trouble of converting from date format into some other type, and presumably back again into date format later, what is the reason for all this effort?

Answer 4

it depends on what you need and how far back your data goes, and also which sql server version you're going to use. 2008 has some new date datatypes that 2005 doesn't have.

data types

Answer 5

The easies option is usually to store the date / time in datetime type column and always store the time in UTC time zone. That way you will never have problem with day light saving. You can then obviously convert the time to whatever timezone the client is using befor displaying the value.

Answer 6

Those who are telling you to just always use datetime didn't read your question thoroughly, and missed the part where you have daylight savings time issues. Normally I recommend datetime myself, but there are cases where that type can fail you.

In this case, since you presumably already have code to handle the int => datetime conversions where needed, you may as well stick with that.

On the other hand, if you're going to sql server 2008 (and not 2000 or 2005), there are new datetime2 and datetimeoffset types that might better fit your needs, and you might consider refactoring.

Answer 7

With SQL Server 2008 you get a plethora of date types:

• SMALLDATETIME 4 byte storage, no second precission
• DATETIME 8 byte storage, 3ms precission
• DATETIME2 has custom precission
• DATETIMEOFFSET has timezone info
• DATE day only, no time
• TIME no day info, only time of day, good for scheduling

So, pick your poison.