My Own WTF

Today I was visiting a client site doing some data mining on a third party product that they own. Ultimately we will be building reports off of their data using SQL Reporting Services. Well, as I was going through the tables, I kept seeing date fields that didn't look like a normal date field. Much to my dismay, this third party company (who uses SQL Server 2000 as a back end database) doesn't believe that the SQL DateTime field is appropriate for storing dates. No, instead they use a long integer data type and store the date as YYYYMMDD.

Not to be out-done, they did this for times too... HHMMSS, ohh, and sometimes they combined the 2 fields... YYYYMMDDHHMMSS... is that not a mess or what. So, for us to do any date comparisons for reporting services, we have to build a view and in that view convert those crappy long integer fields into proper date time fields, well except for when their not NULL!

5 Comments

  • Grrrrrrrrr........ so very smart they are!



    NOT!



    goes right against the ideas of the whole RDBMS and SQL mindset and model.



    someone please findout who did that and have them examined... and castrated so they wont breed!



    silly, won't index right, can't do compares right, looses precision ( fractions of a second are not there)

    and a whole ton of data conversion operators to slow down the code.....



    now I might go for using Julian date and time for some things.... but thats a whole nother deal

    with a big double where the fraction is the time and the whole number is a count of days from a date I can't think of right now. Julian Day / Date formats are used by astonomy folks most commonly.

    some times in history work....

  • I have to work with people who go one step further and introduce magic values to the mix - if it's 99999999 then it's what NULL what normally be and when it's 9999xxxx then xxxx is a form of a serial number to identify records with no dates. Go figure...

  • Actually, storing solely dates like that is very clever. Let me explain. If you store dates in the sqlserver datetime field, you get the time for free. Now, say you want all rows for a given date. How would you select those? Like this? ->

    SELECT * FROM MyTable WHERE Datefield = @dateValue



    No way. You need:

    SELECT * FROM MyTable WHERE Datefield BETWEEN @dateValue1 AND @dateValue2



    because the dates in the database have the time stored inside them. This is cumbersome. Storing dates separately can help in a lot of situations.



    "goes right against the ideas of the whole RDBMS and SQL mindset and model. "

    And why is that? Because you think so? Ever used big iron databases? Oh, these are all wrong of course :)



    Also keep in mind that if you have to support multiple databases in 1 application, you probably want to keep the formats the same. SqlServer's stupid datetime type is not compatible with any other database system for example.

  • Frans, you're not right. If you store only dates and never set the time part then you can use equality to find specific date. And if you're storing time as well you can always use WHERE DATEDIFF(dd, DateToFind, [Datefield]) = 0.

  • Ok, but how are you going to store the 'current' date from say, C#? DateTime.Now? :) That contains time. I know that in T-SQL you get 00:00 for time if you don't specify it, but a lot of date values are set in higher layers of the application.

Comments have been disabled for this content.