Ask the Expert

What are the different date formats in SQL?

What are the different date formats? What is a timestamp? Please give me one example.

    Requires Free Membership to View

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.


This was first published in October 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: