- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
Well, I wouldn't be tha sure about is the 'bad' part. At some point that was the fastest (and even MS-recommended) way of removing the time part from a datetime value.
Admin
Checks in inputs, and responds be silently using the current date... I mean that might be the correct thing to do and it might be behavior one might reasonably expect, or it might not, hard to say without some greater context.
That formatting! Not indenting the WHENs and ELSE the same, and CASE and END... eyes bleeding personally but to each their own.
Finally that date conversion is an idiom that anyone who has been doing "high performance" ETL in SQLServer for a long time knows. That was in all the books. I don't know if its really faster anymore; but old habits die hard.
Admin
SQL Server only fairly recently got separate Date/Time types. Fudges like this were common place, and that's not particularly bad. Obvious what it was doing and I don't think it is going to give unexpected results.
If it's a relic of a Y2k fix then it's just inherited old code (SSIS didn't rock up until around 2005), doing things the way the could be done back then.
Admin
DailyWTF requires more user submissions if this is the level of WTF we are treated to today.
Admin
Code it be the quality of code is improving?
naaaah
Admin
Definitely not.
I've inherited some projects where the Program.cs, which is static by default in C#, was made non-static, so "var p = new Program();" doesn't cause an error. And then, p.someMethod(); is called from the still static Main.
But of course var p = new Program(); is present in non-static methods as well. To call the non-static methods from within a non-static method... or something like that? I really don't know what the original programmer was thinking when he did it like that. My guess is: He simply didn't understand object oriented programming well enough (or, at all).
Admin
The fastest way to strip the time from a DATETIME in T-SQL is DATEADD(DAY, DATEDIFF(DAY, 0, TheDate), 0). This looked insane to me the first time I saw it, until you think about the fact that T-SQL DATETIME is a structure of two 32-bit ints. This expression is simply copying the first int (days) to a new structure where the second int (ticks) is already 0.
Although I will admit to using the CAST(FLOOR(CAST(TheDate AS FLOAT)) AS DATETIME) method myself, while sneering at the idiots who used stupid VARCHAR expressions. Any form of math will always be quicker than making the server allocate and garbage collect strings.
Admin
I've seen the <1950 check (or similar) quite a bit. I once worked with a guy who hated nullable columns. Probably because back in the .NET 1.0-1.1 days, we didn't have nullable value types. So, if you had a datetime column that should have been null (logically), you just used a sentinel value (say 1-1-1945) instead of null. That way your code in c# was simpler. And checks less than a certain value are fine in lots of cases. If your company was founded in the 90's, you won't have purchase order dates from < 1950.
And the casting datetimes as floats is fairly common T-SQL. I would have written it casting the float to an int instead of the floor function, but otherwise the same. It's going away these days with the datetime2 types (you can't cast them to a float, you have to cast to datetime first), but still common.
Admin
The < 1950 check is almost certainly related to the instance-level config option "two digit year cutoff", which defaults to 2049 (meaning that any two-digit year will not be interpreted as < 1950).
Quite HOW it's related to that option I don't know, as there's no converse check for > 2049 and with the option left at the default the < 1950 branch of the CASE statement could only evaluate to TRUE if the parameter argument was sent with a four-digit year anyway. Which makes me wonder whether the output value of the proc is going to end up being stored or used somewhere else in the DB that is subject to two-digit year shenanigans (which is potentially an issue in its own right, as it may imply that nondeterministic date literals/conversions are at large).
Having a stored proc that's basically an independent scalar function is also baffling. It's not even an antipattern, it's just...insane. The only thing it adds to proceedings is a column name for the output, "CurrentDate" — and that's only going to be an accurate descriptor if the procedure was called with no argument, a DEFAULT/NULL argument, an argument < 1950, or an argument evaluating to a datetime within the scope of the current system date.
Admin
Obviously it could just be a case of the sentinel/magic value antipattern as Wyatt mentioned (usual favourite: -1 in int-typed columns…inevitably extended, given enough time, by a -2 value that's a different placeholder for a different sentinel case, and so on and so on) but 1950 specifically struck me as exactly on the mark for that two-digit config option)
Admin
If .NET code is involved this may also be related to .NET not having an actual Date type, just DateTime with the time set to midnight.
Doing it in SSIS I imagine would be a case of just using/converting to the appropriate SSIS datatype, which have pseudo-parity with ODBC/ANSI SQL types and SQL Server types.
I've just looked through the Docs page on SSIS data types and it's over 11 screens long in my browser, with easily 8 of those screens being devoted to the intricacies and pitfalls of using and translating to/from the eight different date/time/datetime types. I (for reals) use the Windows implementation of Python, with its notoriously imperfect standard datetime libraries, for my SQL Server ETL staging and am a million percent saner for it.
Admin
In no way does "Microsoft recommended" imply !bad.
Admin
that just makes it officially bad, nothing else
Admin
I suspect the procedure may have been called with string parameters (sometime in the past - not from SSIS)
SetAsOfDate('95-12-31')
Admin
<quote>The fastest way to strip the time from a DATETIME in T-SQL is DATEADD(DAY, DATEDIFF(DAY, 0, TheDate), 0). This looked insane to me the first time I saw it, until you think about the fact that T-SQL DATETIME is a structure of two 32-bit ints. This expression is simply copying the first int (days) to a new structure where the second int (ticks) is already 0. </quote> You are correct about DATEADD(DAY, DATEDIFF(DAY, 0, TheDate), 0) being the fastest way to strip the time portion, but incorrect about how it does so.
First off, Datediff is a function that returns an integer difference between two dates. It will inherently truncate the result; if you pass it two dates 18 months apart and ask for the difference in years it will return 1 instead of 1.5 . There's also the matter of the implicit conversion from int (0) to datetime (1900-01-01), which happens again for Dateadd. In both places the parameter expected is a datetime so SQL Server effortlessly converts them.
So what happens in the end is: Datediff calculates how many integer days there are between TheDate and a base date, and then Dateadd adds that amount to that exact base date. And if you think about it those parameters didn't have to be 0, that is just a convenient value but any number or date would do as long as the same value is used for Datediff and Dateadd.
Admin
Came to the comments to say the same things as all others, glad I'm not the only one who thought there was no real WTF here. 1950 is clearly due to the default two-digit year configuration on the SQL Server instance (default is 00 - 49 = 2000-2049, while 50 - 99 = 1950 - 1999). The casting to a float to remove the time portion was also the fastest way to trim time from a datetime for a good 20+ years, due to SQL Server (and it's original parent Sybase) storing datetime as days in the integer portion and milliseconds in the fractional portion.
Clearly it was dumb to create a stored procedure to do this logic, but I can see a few scenarios where a SQL function that does it might make sense. For example, if you wanted to use this logic in a lot of different places in your code (from SSIS packages, from stored procedures, from triggers, etc), you want it implemented once and used everywhere.
To be honest, I feel like this post was perhaps written by someone new to database development. The things called out as WTFs are still needed and are still so common in the industry that the submitter will likely write similar code themselves.
Admin
This is kind of crappy, but I can see it as necessary if legacy code. As others have pointed out, you've got the 1950's check probably because somebody was calling this with two digit year strings, and that's the default cutoff; and in earlier versions of SQL Server, there was no DATE type, so you had lots of people chopping times off of datetimes. Also, there were no functions in SQL Server, if you go back enough versions. Could this be ten year old legacy code?
Admin
"Your typical SSIS package starts as a straightforward process that quickly turns into a sprawling mix of spaghetti-fied .NET code, T-SQL stored procedures, and developer tears. "
LOL, thank you for this Remy... Spot on. I have to say I agree with you 100%!
Admin
Yes, I totally agree with you. It should get resolved. Visit https://quickensupports.org/quicken-support-usa/ for accounting software issues.