• Romeo (unregistered)

    Dude, that's wild...

    Thanks God he started in 1990...

  • Anon (unregistered)

    Why did he end at 2100? Does he know something we don't?

  • RyGuy (unregistered)

    Are you kidding me?

    W.
    T.
    F.

  • Joe (unregistered)

    Anon: Haven't you read Nostradamus's predictions?

  • Guayo (unregistered)

    Wow! Nice solution... I think I'm gonna use that tblDateYearWeekDay for my next project, just a little question, how do I fill the table?

    ...

    ...

    Hahh! I got it! I'm gonna make a script to fill that table, all that I need is to call DatePart in a loop, it was easier than I thought...

  • Peter (unregistered)

    Guayo! Step Away from the keyboard with your hands in the air!



    :)

  • Kofi Anon(ymous) (unregistered)

    WTF is that?
    WTF was he thinking?
    WTF does he still have a job?

    Ive done some odd things with date functions... but this is uh... It almost transcends WTF.

    Is the datetime field the primary key? can you do that?

    What does he do to turn int into day of week? Bet theres another table...

    ...
    3 Wed W
    4 Thur T
    5 Fri F
    ...

  • G Dawg (unregistered)

    I would be careful about replacing usage of that table with DATEPART functions.

    It is entirely possible he is using some non-standard dates, or at least leaving open the possibility to do so. I wouldn't necessarily want to give up that flexibility.

    Faster is not always better... At the very least, this requires a full regression test.

  • Jake Vinson (unregistered)

    No one is defending the obvious reason this could be a good implementation?

    This is the best way to handle dates and times if you're a total goddamn retard.

  • brianw13a (unregistered)

    Hey - what's the story on the redneck. I see mentions of him from time to time.

  • e. thermal (unregistered)

    Tom's solution would not have worked for the company that I worked for. They had a 4-4-5 calendar, and because of that they needed a correction week every seven years. So any of the built in functions that gave you a week number could not be used. That 53 week year every 7 years caused a lot of grief. The use of a table is silly but has anybody looked at the table data? It is possible some dates where manually hard coded to specific weeks/years etc. So Tom's solution could cause more problems then it solves. We may have to see the spec before we pitch this programmer into the fire.



  • Stan Rogers (unregistered)

    It's ... almost ... beautiful. Transfixing, in any case. Sort of like the best of Rube Goldberg's contraptions.

    I've always believed that the best way to get anything done is to give a lazy man a tight deadline. This guy obviously has too much get-up-and-go for his own good.

  • Nugget (unregistered)

    Clearly this table should be optimized by moving the DateYear values out to a lookup table. I'm sure that'll fix their long-running queries!

  • Jeff S (unregistered)

    Actually -- believe it or not -- if that table was properly indexed on the WeekDay and Year columns, and RegDatum was also indexed, a simple join to that table would FAR outperform the "solution" of:

    AND DatePart(WEEK,regdatum) = @WEEK
    AND DatePart(YEAR,regdatum) = @JAAR

    The above is actually a WTF in itself if this was a large database -- if regdatum is indexed (as key date columns often should be), the above WHERE clause cannot make use of it and must retrieve EVERY row in the table and evaluate the two functions. It would not be an optimal solution; as all of us "experts" here at TheDailyWTF know, that would be to calculate the startdate/enddate range of the desired data and retrieve rows within that range like this:

    AND RegDatum BETWEEN @Start AND @End

    Causing index seeks on RegDatum and returning the rows very quickly.

    By the way -- while the range and size of this
    "table of dates" is probably a little insane, tables of dates can be extremely handy, and often is the only way to produce resultsets dynamically for every day in a date range, even when no data exists. (which can be a very common request). You will often see tables of numbers (often called Tally tables) in many databases for some advances calculations and reporting which would otherwise require cursors and other WTF-esque constructs.

  • Rob (unregistered)

    He'll be having th last laugh if we move to a decimal week!

  • drift (unregistered)

    WTF...?

    @Jeff S: the people never thought about Y2K - so why care about 2100 + n? ummmm.... WTF?

  • Jeff S (unregistered)

    @drift -- apples to oranges my friend. not only are we talking about code written in 1980 that must run 20 years later vs. code that must run 100 years later, but we are talking code vs. data.

    Y2K : all programs had to be rewritten, recomplied, data files altered, etc.

    This : some rows need to be added to a table.

    Not a good comparison. This is a little easier to fix a hundred years from now!

  • drift (unregistered)

    @Jeff: so it's still no good software design - and remember: speed might be important, but it isn't everything that counts.

  • Jeff S (unregistered)

    @drift --
    >>so it's still no good software design

    huh? What's not good software design? the existance of this table? the ideas I presented in my first post?

    not sure what you are referring to.

  • Bergstrom (unregistered)

    I'm just glad he coded this to run to 2100, because any good engineer knows that code has to be written to survive for a hundred years. ^_^

  • Guayo (unregistered)

    @Jeff S

    I seriously doubt that if that table was properly indexed a simple join would "FAR outperform" using DatePart. Unless I'm missing something really obvious (like if the SQL Server DatePart implementation sux) I can't believe a table access (indexed or not) would be faster than a function call (AFAIK, IO access still is orders of magnitude slower than normal computational tasks)

  • Jeff S (unregistered)

    Guayo --
    You missed the entire point. you're right -- I/O is much slower than a function call.

    Using an index results in exactly as many I/O operations as you need. If 5 rows match the criteria, 5 I/O's are used (essentially).

    Not using an index -- i.e., using a function (datepart) that will require a calculation to be performed on every value in a table or in an index -- requires an I/O operation for EVERY VALUE IN THE TABLE. if 5,000,000 rows are in the table, and 5 rows match the criteria, 5,000,000 I/O's are still performed.

    let me give you an analogy: using a regular old book index in a large textbox, which would you be able to do faster: give me the page numbers of all key words starting with the letter "e", or the page number of all key words in which "e" is the 5th letter? Do you understand the difference, and how this analogy is relevent to the problem?

  • Bill (unregistered)

    I would have coded this to the year 2200, just to be on the safe side.

  • Bustaz Kool (unregistered)

    Jeff S

    >>>>
    Actually -- believe it or not -- if that table was properly indexed on the WeekDay and Year columns, and RegDatum was also indexed, a simple join to that table would FAR outperform the "solution" of:
    <<<<

    You took the words right out of my mouth. The existence of this table, per se, is in no way a WTF. A table of dates and even a table of integers is a perfectly prudent thing to have and leads to some wonderfully elegant solutions that are EFFICIENT and ACCURATE.

    The WTF here lies entirely in the poor indexing applied to the table and the the fact that the SELECT is performing a subquery rather than a join.

    The secondary WTF here is the number of people who feel that the table is the problem. This can be attributed to the following axioms (held to be self evident):

    1) MOST People don't know how to use a database. (No real surprise here)

    2) MOST Programmers don't know how to use a database (Still no real surprise)

    3) MOST Database programmers don't know how to use a database (A little surprising, perhaps)

    4) TOO MANY DBA's don't know how to use a database (Professional misconduct but not that great a surprise)

    5) The state of the art in databases is, with some exceptions, pretty darn good. Room for improvement? Damn right but still pretty darn good. The state of affairs (actual common usage) is pretty damn sorry.

    Yes, you can quote me on that!

  • drift (unregistered)

    not long ago software developers doubt, that software would run for more than 20 years...

  • Guayo (unregistered)

    @Jeff S... again

    I just undestand what you mean... You mean that if regdatum was indexed and that index were used in the query then the db engine would have to read less rows in the table instead of a full scan. However there could be an index on regdatum and still the db engine could chose another index (a key maybe) and then that tblDateYearWeekDay table would give you cero performance improvement.
    In the query posted it is shown an additional column used to discard records, that column could very well be indexed.
    So ye,s you may be right that using such auxiliary table could improve performance in some queries, but not always, and let's not forget the cost of having an extra index. I can't agree with you that using DatePart is a WTF instead of the table as you (as well as I) don't know what indexes those db have. However if you say that using DatePart no matter what is a WTF then I would suggest you do some performance tests to find out.

  • Bustaz Kool (unregistered)

    >>>Donald K. Burleson<<<

    I love the fact that his dress code is so exacting on one page and yet he has taken to working in the nude on another page.

  • Jeff S (unregistered)

    @Bustaz Kool


    Very well said, and very true .....

    That's a common thought, from non-database programmers to say stuff like "the datePart function must SUCK if it can't use an index! what a dumb computer".


    Guayo -- swing by my blog I'll put some sample code up for you. should be up in a half hour or so -- do you know how to you query analyzer to look at execution plans and all that?

  • Bustaz Kool (unregistered)

    Assuming that regdatum has an index applied to it. Using the value in a function renders it non-sargable. This means that what Jeff S is saying is absolutely true. Now you have the overhead associated with the index but none of the benefits.

    As to the overhead itself:

    The table is fairly static in nature. We are not going to be adding deleting or updating this table for essentially one hundred years (ish). A table of a few thousand records just isn't that big of an entity to concern ourselves with. If you're really that worried about the size of the index itself, make it a clustered index.

  • Phil Scott (unregistered)

    Just throwing this out there: how about an index on a computed column?

  • Jeff S (unregistered)

    @Phil -- that would work, too. Or perhaps an indexed View.

    Again, the only point of most of these comments isn't to defend this particular piece of code per se, but the knee-jerk reaction of many (including DBA's) when they see tables like this in a database and don't understand the (huge) potential benefits of setting stuff like this up.

    It's funny, some here are making fun of this guy for expecting his program to still be running in the year 2100 (as if his code will last THAT long!) and others are saying it is a WTF because it won't work Jan 1st, 2101 !!


    This site cracks me up sometimes .. always entertaining ...

    (by the way, my weblog post with some sample code is up, so check it out for those who are curious ... )

  • Bustaz Kool (unregistered)

    Phil Scott

    >>>index on a computed column? <<<

    Yes, you can do this, at least in some products, but what's the upside? The table already exists and I assume has accurate data.


  • Guayo (unregistered)

    @Jeff S

    I posted in your blog but I’ll do here to.
    First. I don’t have SQL Server at hand from where I’m posting (just Informix and Oracle, that BTW supports function-based indexes).
    When I first posted here I didn’t realise that as you said with proper indexation that query would perform better and even it could out-perform using datepart). I do see that know, however I don’t agree with you that using datepart is a WTF in itself). I don’t know if you fail to see that the query posted in today WTF includes an additional column in the condition. That column could indexed and that index could be a better way to scan the tblTijdregistratie table instead of the dates condition. Maybe I’m still missing something?

    Anyway. I’m a little rusty at T-SQL today but I wonder if that the condition could be redone using the Datepart function making the date condition clause sargable? (Like creating a date from the @week and @jar parameters and the missing part of the date obtained from the regdatum column).

  • Guayo (unregistered)

    I repeat, I don’t have SQL Server at hand but. Wouldn’t this statement be equivalent to what was posted as the fix but in this case sargable for an index on regdatum?

    regdatum = dateadd('dd', datepart('dd', regdatum), dateadd('ww', @WEEK, convert('01/01/'+@JAAR, 103)))

    I could be totally wrong and that condition could be a WTF so this is a good place to post it.

  • Just Some Guy (unregistered)

    Sure the code is bad, but creating a date table like this is often a very good solution (Jeff S and Bustaz Kool have pretty much gone over the reasons for that). In fact, this is very standard for data warehouses, and I'd go so far as to say that a data warehouse that doesn't use a date table is poorly implemented.

    It doesn't excuse the bad code, but having a date table is definitely not a WTF.

  • Guayo (unregistered)

    upss... replace
    convert('01/01/'+@JAAR, 103)
    with
    convert(datetime,'01/01/'+@JAAR, 103)

  • Bustaz Kool (unregistered)

    So here's what ya do....

    Alter the tblDateYearWeekDay to hold two timeless dates (Today and Tomorrow).

    Re-write the sproc as follows:

    CREATE PROCEDURE spAkkoordRegCtrl (
    @RegWerknemer int,
    @WEEK tinyint,
    @JAAR smallint
    ) AS
    begin
    UPDATE tblTijdregistratie
    SET RegAkkoord = 1,
    RegFix = 1
    WHERE exists(
    select *
    from dbo.tblTijdregistratie a
    join dbo.tblDateYearWeekDay c
    on a.RegDatum between c.Today and c.Tomorrow
    where c.DateYear = @JAAR
    and c.DateWeek = @WEEK
    )

    Put a clustered index on dbo.tblDateYearWeekDay (Today) and yer good to go...

    No date arithmetic, no multiple subqueries to access the same row, no muss, no fuss.

    Enjoy the weekend, y'all.

  • Sjoerd (unregistered)

    ...ashamed to be Dutch right now.

    Anyway, it's obvious what s/he wanted to do... they just did half of it.

    Here's mine:

    Create Table dbo.Calendar
    ([Year] SmallInt Not Null,
    [Month] TinyInt Not Null,
    [Day] TinyInt Not Null,
    DayOfWeek TinyInt Not Null,
    WeekOfMonth TinyInt Not Null,
    IsWeekDay Bit Not Null,
    IsWeekend Bit Not Null,
    IsBusinessDay Bit Not Null,
    IsHoliday Bit Not Null,
    HolidayID SmallInt Not Null,
    ShortName As
    (Cast(Right('0' + Cast([Month] As VarChar), 2) + '/' +
    Right('0' + Cast([Day] As VarChar), 2) + '/' +
    Cast([Year] As VarChar) As Char(10))),
    LongName As
    ((Case DayOfWeek When 1 Then 'Mon'
    When 2 Then 'Tues'
    When 3 Then 'Wednes'
    When 4 Then 'Thurs'
    When 5 Then 'Fri'
    When 6 Then 'Satur'
    When 7 Then 'Sun' End) + 'day ' +
    Cast([Day] As VarChar) + ' ' +
    (Case [Month] When 1 Then 'January'
    When 2 Then 'February'
    When 3 Then 'March'
    When 4 Then 'April'
    When 5 Then 'May'
    When 6 Then 'June'
    When 7 Then 'July'
    When 8 Then 'August'
    When 9 Then 'September'
    When 10 Then 'October'
    When 11 Then 'November'
    When 12 Then 'December' End) + ' ' +
    Cast([Year] As VarChar)),
    DateValue DateTime Not Null,
    PrettyName As
    ((Case DayOfWeek When 1 Then 'Mon'
    When 2 Then 'Tues'
    When 3 Then 'Wednes'
    When 4 Then 'Thurs'
    When 5 Then 'Fri'
    When 6 Then 'Satur'
    When 7 Then 'Sun' End) + 'day ' +
    (Case [Month] When 1 Then 'January'
    When 2 Then 'February'
    When 3 Then 'March'
    When 4 Then 'April'
    When 5 Then 'May'
    When 6 Then 'June'
    When 7 Then 'July'
    When 8 Then 'August'
    When 9 Then 'September'
    When 10 Then 'October'
    When 11 Then 'November'
    When 12 Then 'December' End) + ' ' +
    Cast([Day] As VarChar) +
    (Case [Day] % 10 When 1 Then (Case When [Day] = 11 Then 'th' Else 'st' End)
    When 2 Then (Case When [Day] = 12 Then 'th' Else 'nd' End)
    When 3 Then (Case When [Day] = 13 Then 'th' Else 'rd' End)
    Else 'th' End) + ' ' +
    Cast([Year] As VarChar))
    )
    Go

    This table is SPECTACULARLY useful in queries such as "give me the number of business days between two days", etc.

  • Gene Hamilton (unregistered)

    Or he could have used the Doomsday Algorithm
    http://en.wikipedia.org/wiki/Doomsday_algorithm

    I figure if you could write code to populate that table with dates you probably could use this algorithm just as easily.

  • Jeff S (unregistered)

    Thanks for checking out the blog, Guayo !


    The potential use of ANOTHER index, unfortunately, was not your original point and had nothing to do with my original comments -- that was revived quite a bit as you went along.


    You didn't mention that other index situation until your last post before I put this all up; and that certainly was never MY point, and has absolutely nothing to do with any of my comments!


    It is simply this: correctly using indexes and/or setting up a proper WHERE clause that is sargable will ALWAYS use the maximum available indexes, regardless of how the optimizer now or later or some other time in the future decides to pick its indexes for a particular query. It will ALWAYS outperform or at the WORST be the same as a condition like WHERE SomeFunction(SomeColumn)=@X -- no exceptions.


    If another situation might occur in certain cases makes the performance the SAME, well that's fine for you, but I'm going to stick with writing highly optimized code for all possible situations.


    If you'd like to see various uses for Tally tables or Date tables and how then can make SQL statements much more elegant and efficient, let me know and I dedicate my next blog to you with some examples. Check out the SQLTeam forums (or ask some questions there) for more info and guidance as required.

    As a side note, from one of your later posts:

    >>Anyway. I’m a little rusty at T-SQL today but I wonder if that the condition could be redone using the Datepart function making the date condition clause sargable? (Like creating a date from the @week and @jar parameters and the missing part of the date obtained from the regdatum column).

    You also aparently missed my comments about the benefit of calculating the StartDate and EndDate and using a sargable BETWEEN clause -- that is exactly what I was doing, and why I put that in my benchmark examples and indicated it was the BEST solution in the most situations for this type of query.

    (an equals condition, as you indicated in one of your last posts, will not work in this case since the data being returned is for an entire week and not a single day, so a range is required)

    Let me know if you have any other questions or comments.

  • jeff S (unregistered)

    (sorry -- lots of typos in that last one -- it too late to be posting on Friday night !!)

  • Miles Archer (unregistered)

    Everyone knows that the world is going to come to an end in 2037 when the Unix clocks roll over. Why include dates past then?

  • Jim (unregistered)

    Jeff S: Regarding your "too late to be posting on a Friday night" comment...think about it. How many people here were continually refreshing this page (late on Friday night) just to read the latest and greatest wisdom from their fellow programmer? Probably a lot. And everyone knows it's not "cool" to be home on a Friday night...especially on your computer...reading about what someone else programmed. That's pretty geeky...and a little sad? I really can't talk because I was one of those closely following this discussion...late on Friday night...but it's an interesting observation nonetheless.

  • P (unregistered)

    actually it's not that stupid idea. once i worked on a system where the client used a different week numbering than in the real world ... creative accounting :) - in that system we had to remap days to a predefinied week

  • xyz (unregistered)

    I think he used a table for a future change in our calendar... Alien invasion, Klingon world domination... Who knows ? Maybe in 2099 won't be a fixed number of day per week :)


  • Cliff (unregistered)

    just a little question, how do I fill the table?


    manually - get a temp in, and set em typing...
  • Cakkie (unregistered)

    My guess (I submitted this code) is that he did not know about the FirstDayOfWeek parameter of DatePart, nor of the language settings in SQL and Windows that can change this. Here in Belgium weeks start at Mondays, not at Sundays (which is MSSQL Server default in not set otherwise).

    As for indexes, it's quite possible that a JOIN would be faster in this case (DatePart are indeed extra function calls), but since the code only runs one time at a time, and already executes in less than 100ms, the performance gain I would get would probably be neglectable, and imho to small to justify the use of an extra table containing nothing else than static data.

  • curry684 / Niels (unregistered)

    I've seen the exact same table in a production system with a slight twist: all quarters from 2000 to 3000, so like this:

    1 2000 1-1-2000
    2 2000 1-4-2000
    3 2000 1-7-2000
    and so on...

    Wicked :)

  • Guayo (unregistered)

    @Jeff S
    The point I'm trying to make is that although sometimes there is necessary an auxiliary table to speed up or simplify querying a date range, in this particular example you can do that without the need of such table. So the thing I didn't agree with your post in the daily WTF is that using Datepart function it's a WTF with a large database. As I said sometimes your date range is not the best way of discard records in your query so an index in that column plus the extra table won't give you nothing. But once you got all those extra db objects you will always have to pay the cost they impose.
    But if we talk only of the example in the daily WTF what I can say is that there is a way to calculate the starting and ending date of a week of a year just using build in functions, so for this scenario an extra table isn't justified.
    For example:

    case
    when @week = 1 then convert(datetime, '01/01/'+cast(@a as varchar), 103)
    else 1+((@week-1)*7) + convert(datetime, '01/01/'+cast(@jaar as varchar), 103) - datepart(dw,convert(datetime, '01/01/'+cast(@jaar as varchar), 103))
    end

    will give you the start date of a given week of a given year. If you wrap that code in a UDF you could reuse that code easily across your queries. The code for get the end date of a given week it's similar. SO why do we need an extra table in this case.
    As I said sometimes such auxiliary tables are useful, I don't like them a lot as they are just a hack but hacks are sometimes the easy way of do something, in this case it's not the case IMO.

  • Jeff S (unregistered)

    @Guayo --

    you're not following anything I've posted so I guess I need to give up .....

    I don't recall EVER saying we NEED an extra table ... and I recall expliciting describing the EXACT notion you've just posted (actually, I've tried to get you to notice that TWICE now). If you decide to reread all my posts carefully and respond, that would be great, but until then I will have to accept the fact that I am not able to convey these concepts to you so I will just have to let it go.

Leave a comment on “The Land Before DateTime Functions”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article