SQLserver date manipulation and formatting

MicroSoft SQLserver has some pretty slick features, but one area where they are definitely sucking is date manipulation and formatting. [Joseph complained about this](http://joseph.randomnetworks.com/archives/2006/06/22/sql-server-convert/) a while ago, but it’s time to me to chime in now.

My biggest complaint is the half-assed implementation of a reasonable design: Instead of having separate “date” and “time” datatypes, they provide a single “datetime” type. As far as I’m concerned that’s a good thing since it merges three separate types into one. But it’s an incomplete implementation because there isn’t a simple way to extract the date or time from a datatime type. There *are* functions to extract individual “fields” of the date or time, e.g. day of the month, hour of the day, etc. What I’d prefer would be a function(s) that would return *only* the date and/or time from a datetime agglomeration.

I’m currently trying to write a query that goes back to the beginning of the sixth month in the past, in order to implement a “recent orders” view. If I use the dateadd() and getdate() functions, I can get pretty close to my goal:

select dateadd( mm, -6, — Go back 6 months from…
getdate() + 1 – day(getdate())) — 1st of this month

The problem with the code above is that it returns the correct *date*, but the time part will be set to whatever time this code was executed. If I run this query at 12:34 in the afternoon, I won’t see orders placed on the morning of the first day I wish to report. The easiest way I’ve found to get around this to use convert() *twice*: once to strip off the time (returning a varchar), and again to convert the varchar back into a datetime:

select convert(datetime, — Convert back to datetime
convert(varchar(11), — Truncate off the time
dateadd( mm, -6, — Go back 6 months from…
getdate() + 1 – day(getdate())) — 1st of this month

The default format for converting datetime to/from varchar is “Mon DD YYYY HH:MIAM”, so above I’m grabbing the first 11 characters (“Mon DD YYYY”) of the converted string and passing them right back to convert() and asking for a datetime. Since I haven’t supplied a time, it defaults to midnight.

About Jim Vanderveen

I'm a bit of a Renaissance man, with far too many hobbies for my free time! But more important than any hobby is my family. My proudest accomplishment has been raising some great kids! And somehow convincing my wife to put up with me since 1988. ;)
This entry was posted in Uncategorized and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *