What are the different date formats in SQL?

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT SQL How to check SQL query construction with the Mimer Validator Using the SQL GROUP BY clause for counting combinations How to use an SQL CASE expression How to sort an SQL UNION query with special ORDER BY sequence How to use string functions to make an SQL join An SQL solution for a customer order homework problem How to use SQL's POSITION function with substrings Using SQL date functions to get totals for last three days Using CASE in the SQL ORDER BY clause What's the difference between an SQL inner join and equijoin?

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

Among the many popular database management systems in use today, there is more diversity in the datatypes used for dates and times than any other type of data.

DATE datatypes are used to store dates. These are the dates we are all familiar with, such as 2007-02-28, 1991-09-09, etc. This particular format is also one of the International Standards Organization (ISO) standard formats for representing dates. (Yes, there are others. For example, 2007-10 is a valid ISO date format.) For the moment, though, we shall discuss not the displayed formats of dates and times, but just what they can contain.

Some databases offer DATE datatypes, others do not. For example, Microsoft SQL Server has no DATE datatype, but does have DATETIME and SMALLDATETIME. As these datatype names suggest, they include a time portion, which is accurate to 3 milliseconds. The difference between them is the range of valid dates that they support. DATETIME covers the years 1753 through 9999, while SMALLDATETIME covers only 1900 through June 6, 2079. When you insert a value such as '2005-05-05' into a DATETIME or SMALLDATETIME column, the time portion is automatically set to zero (midnight).

Oracle, on the other hand, uses the DATE datatype to include both a date and a time. MySQL, meanwhile, has separate DATE and TIME datatypes, as well as DATETIME.

Timestamps can be datetimes, or something else entirely. The standard SQL CURRENT_TIMESTAMP function is simply the current datetime value, just as CURRENT_DATE is the current date value, so in that sense, the timestamp is a datetime.

But timestamps can also represent other values. Many PHP programmers routinely use the Unix epoch time as a timestamp, storing the value in an INTEGER column. The Unix epoch time is simply the number of seconds elapsed since midnight on January 1, 1970. The advantages of this format for storing datetimes include portability across different platforms, and ease of calculating differences (in seconds). Disadvantages include not being able to recognize the actual date of a value like 1189370937, as well as the Year 2038 problem.

And to totally confuse matters, Microsoft SQL Server also has a TIMESTAMP datatype, but this has nothing to do with actual dates and times.

Your original question asked about date formats. As you might guess, there is even more diversity in how the various database systems can display dates and times than in the datatypes used to store them. Oracle uses the TO_CHAR function, MySQL the DATE_FORMAT function, SQL Server the CONVERT function, and each has a bewildering array of options. At this point, your best bet is to dive into your SQL Reference Manual and start testing.