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.
