autoexcrement (12/9/2010)
1. Craig, can I have an autographed photo of your brain for Chistmas?2. This is totally cool. I'm trying to figure it all out. I think I'm doing pretty well. Couple questions/confirmations (re: Post #1032106):
CONVERT( DateTime, CONVERT( VARCHAR(20), DateKey)) AS DateKeyI guess this means you can't directly convert an INT to DateTime?
The answer to this is "it depends" - specifically on what you're storing in that int. If you are storing a date in YYYYMMDD format, then no. If you are storing an offset from 01/01/1900, then yes.
DATEADD( mm, DATEDIFF( mm, 0, CONVERT( DATETIME, CONVERT( VARCHAR(20), DateKey))), 0) AS MonthStartDateKeyOkay, this confuses me. I have taken it apart from the inside out, I understand what DATEADD and DATEDIFF do, but I don't understand the use of "0" here...?
If you were to run "SELECT CONVERT(DATETIME, 0)", you will see that this is converted to 01/01/1900.
What the code is doing is getting the number of month boundaries crossed between 01/01/1900 and the other date. It then adds this number of months to 01/01/1900. The end result of this is that it will always return the first of the month for the specified date.
And the
REVERSE, STUFF, REPEAT..., as much as I'd like to pretend to understand it, I don't.If you feel like elaborating on any of this, I'd love to learn what exactly you're doing.
The REVERSE/STUFF/STUFF/REVERSE is taking the TimeKey field (time represented as an integer). Since leading zeros are not shown, anything before 10am will only be 5 digits instead of 6. So the intent is to add the colons between the hours/minutes and minutes/seconds. SELECT REVERSE('80101') will return 10108. We now can put those colons in the proper places. (STUFF adds the colon in the proper position.) Once finished, you have 10:10:8. You now need to reverse this again to have the valid time: 8:01:01.
Edit: you could also do STUFF(STUFF(RIGHT('0' + CONVERT(VARCHAR(6), TimeKey), 6),5,0,':'),3,0,':'). This converts the TimeKey to a string, prefixes it with a '0', takes the right 6 characters, and adds the colons.
Does this explain it all?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes