SonicChicken blog

MicroSoft SQLserver has some pretty slick features, but one area where they are definitely sucking is date manipulation and formatting. Joseph complained about this 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.