A Date with SQL - Part 1: Date Formats

I've decided to do this as a three part article on working with dates in SQL. Why? For the fun of it, I suppose.

You know, I never understood why working with dates in SQL had to be so darn difficult. I would think something as important as dates would require a bit more attention from the developers, but I guess that's what user defined functions are for. Since I started doing web development here in the office, I've been asked to work with dates a lot. Now people who are designing other apps and functions are coming to me for advice on working with dates in SQL. Kind of reassuring in a way.

So here's a few things you can do to simplify your life in SQL, where working with dates is concerned. SQL is on one line, and output on the next, where I can get blogger to display it properly...

We'll start by just getting the current date, using SQL's native DATETIME data type:

select getdate()
2005-06-24 07:22:04.577

Just a timestamp, and in a good format for calculating. Largest to smallest increment, left to right, like a decimal number. Interesting that we usually read our timestamps as month, day, year, hour, minute, second, when the mathematically correct way to do it would be year, month, day, hour, minute, second.

SQL actually has two native date and time data types, DATETIME and SMALLDATETIME. DATETIME is accurate to the millisecond, while smalldatetime is accurate tot he minute. Show you that in a second... (get it?)

Anyway, now that we have a date, we can use the CAST function to get it into any number of formats other than DATETIME:

select cast(getdate() as smalldatetime)
2005-06-24 07:22:00

select cast(getdate() as char(19))
Jun 24 2005 7:25AM

Or if you just want the date...

select cast(getdate() as char(11))
Jun 24 2005

SQL also has another format called TIMESTAMP that has absolutely nothing to do with dates or times, and is not used to determine the time of anything. It's a randomly generated binary number, although usually represented in hex. interestingly enough, you can cast datetimes as timestamps, as well as other non-date data types:

select cast(getdate() as timestamp)
0x0000967D007C17C7

select cast(getdate() as int) /* also same output for DECIMAL, and NUMERIC types */
38525

Now that we've gotten some formats we can use, we can look at how to calculate the difference between these dates, accurately, and quickly, in the next article.

See you soon.