Wednesday, December 01, 2004 4:14 PM szurgot

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)

 

Comments

# re: I-Hate-SQL-Server Club (Ok, Not Really)... But I do hate it's date handling

Wednesday, December 01, 2004 4:46 PM by Denny

as a long time SQL user I hear ya!

I am kind of thinking of doing a SQL 2005 date thing like this if they have not done it in 05.

05 lets us write code that "looks like" a coulmn in the results set.

I am thinking:

Date - no time just the date
Time - no date just a Time Of Day as HH:MM:SS.xx
and two variations:

Set/Get Date
Set/Get Time
Set/Get [Part] from Column

so the first 2 would be a "real column" in the table that only exposes a new type
the last one would be a "Virtual" coulumn that computes the result based on the value of another coulmn that has a standard datetime data type.

what do you think of this idea??

# re: I-Hate-SQL-Server Club (Ok, Not Really)... But I do hate it's date handling

Wednesday, December 01, 2004 5:33 PM by Derick Bailey

AMEN, BROTHER!!!!!

I can never remember that stupid date time conversion code... and i'm constantly having to do that conversion so i can compare two DATES to see if the DATE is the same, not the date and time...

I long for SQL Server 2005 so we can have an actual DATE field and a seperate TIME field....

# re: I-Hate-SQL-Server Club (Ok, Not Really)... But I do hate it's date handling

Wednesday, December 01, 2004 5:50 PM by Kent Tegels

I hate to break this to you Derick, but Date and Time types have been removed from SQL2005 from B2 forward.

# re: I-Hate-SQL-Server Club (Ok, Not Really)... But I do hate it's date handling

Wednesday, December 01, 2004 6:03 PM by MartinJ

I hear you about being able to strip the time off a date field. Doing a comparison though is easier just doing a datediff(d,date,date) = 0 comparison.

I agree that with 2005 it should be possible to handle dates much better. Even though the two types have been removed, you could always add them back as custom types (with the full set of supporting boilerplate code you need to completely integrate them). Heck, you might be able to have dates that have a much larger range than the existing one by removing resolution (don't need seconds or how about minutes? Woohoo, added a bunch more days and years possible to the range).

Ah well. I wonder how long it will be before someone comes up with an assembly chock full of date handling goodness.

# re: I-Hate-SQL-Server Club (Ok, Not Really)... But I do hate it's date handling

Wednesday, December 08, 2004 2:45 PM by Anna Shchurova

I agree that the date handling is not the best in SQL. Also a lot of people make mistakes when they try to compare dates in stored procedures to select data in teh specific date period - they covert dates to varchar type and then compare strings - not very smart.

# Random Recent Oracle Stuff

Wednesday, December 08, 2004 3:17 PM by TrackBack