Wednesday, December 01, 2004 4:14 PM
I-Hate-SQL-Server Club (Ok, Not Really)... But I do hate it's date handling
Ok, Mr DailyWTF decided to start up an I-Hate-Oracle Club. There's lots of reasons to hate Oracle. There's also lots of reasons to like Oracle.I'm not going to go into that....
I cut my teeth on Oracle. Used it for about 10 years. Only been using SQL Server for about 4. Sql Server is great. It's fine. Runs pretty fast, and I can optimize it well, but it's got some idosynchrosies that don't make a whole lot of sense.
And the ones that's killing me right now is Date handling!!!! Why is it so hard to work with dates???!!!
Why doesn't GetDate() return the date at midnight?
Or why isn't there a separate function to return the time now and the time at midnight
Or why isn't there an easy way to get the time at midnight on any given day?
The Sql Server 200 Resource Kit says that Date Round and Date Truncate functions are handled by CONVERT, which means if I want to get midnight, I have to do this: SELECT CONVERT(datetime, CONVERT(varchar(10), getdate(), 101)) You tell me that's intuitive? Convert to a character string to truncate the time portion, then back? I found a web site that displays this method, that makes a little more sense SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0), seeing that there's no conversions involved, just calcuations, but it still seems counterintuitive to have to do calculations on a concept so common it should be a native part of the story.
Ah well, enough ranting. I can't create a GetTodayAtMidnight() Function because it would be nondeterministic, but I could create a GetDateAtMidnightFunction(Date) which would be close enough, so that's what I'll do.. Wonder why I didn't think of that before.
(And I still think Date handling should be expanded in SQL. It should be easier to truncate dates without having to resort to conversions and manipulations)