April 12, 2010 at 10:55 am
I actually found such a formula myself by modifying code found at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471
Here is the code:
DECLARE @IntDate INT
SELECT @IntDate = 20090117
dateadd(month,((@IntDate/10000-1900)*12)+((@IntDate/100)%100)-1,(@IntDate%100)-1)
But, the performance of this expression is actually slightly worse than the simple code posted by Jeff.
/SG
April 12, 2010 at 12:41 pm
Stefan_G (4/12/2010)
I actually found such a formula myself by modifying code found athttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66471
Here is the code:
DECLARE @IntDate INT
SELECT @IntDate = 20090117
dateadd(month,((@IntDate/10000-1900)*12)+((@IntDate/100)%100)-1,(@IntDate%100)-1)
But, the performance of this expression is actually slightly worse than the simple code posted by Jeff.
/SG
yeah... that's kind of like the one I was thinking of. I never did get around to testing it for performance and it was supposed to be faster because it had no character conversions. Still haven't tested it but I guess I have a reason to, now.
Thanks, Stefan.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 13, 2010 at 8:32 am
Why bother converting the int to date at all? Every human can read and understand 20100411 is April 11 2010. 🙂 And if you need a 'pretty' date, do it on the front-end side...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2010 at 10:25 am
TheSQLGuru (4/13/2010)
Why bother converting the int to date at all? Every human can read and understand 20100411 is April 11 2010. 🙂 And if you need a 'pretty' date, do it on the front-end side...
OK, I'll bite. I admit that I cannot tell if you are joking here or not? :unsure:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2010 at 11:06 am
RBarryYoung (4/13/2010)
TheSQLGuru (4/13/2010)
Why bother converting the int to date at all? Every human can read and understand 20100411 is April 11 2010. 🙂 And if you need a 'pretty' date, do it on the front-end side...OK, I'll bite. I admit that I cannot tell if you are joking here or not? :unsure:
Not really. I have some data warehousing customers that do present ints such as this directly on output, and some that format the value in the report itself to something like mm/dd/ccyy.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2010 at 11:32 am
TheSQLGuru (4/13/2010)
RBarryYoung (4/13/2010)
TheSQLGuru (4/13/2010)
Why bother converting the int to date at all? Every human can read and understand 20100411 is April 11 2010. 🙂 And if you need a 'pretty' date, do it on the front-end side...OK, I'll bite. I admit that I cannot tell if you are joking here or not? :unsure:
Not really. I have some data warehousing customers that do present ints such as this directly on output, and some that format the value in the report itself to something like mm/dd/ccyy.
Ah. Well, I am not usually a stickler for constraints and data validation, etc., but Dates and Times are my one exception.
In my experience, any Date/Time column that allows invalid values *will* contain invalid values. I am not sure why date/time data is so much more susceptible to this than any other type (though I suspect it has to do with how many different forms and formats they have and how many different ways there are to convert them from one datatype to another), but they most definitely are.
And unlike most other invalid-data incidents, I have always found that this one would have been far easier to fix on the front-end, than on the back-end. So my recommendation has always been to put Dates/Times in Datetime type fields.
All IMPO, of course. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 13, 2010 at 12:15 pm
RBarryYoung (4/13/2010)
TheSQLGuru (4/13/2010)
RBarryYoung (4/13/2010)
TheSQLGuru (4/13/2010)
Why bother converting the int to date at all? Every human can read and understand 20100411 is April 11 2010. 🙂 And if you need a 'pretty' date, do it on the front-end side...OK, I'll bite. I admit that I cannot tell if you are joking here or not? :unsure:
Not really. I have some data warehousing customers that do present ints such as this directly on output, and some that format the value in the report itself to something like mm/dd/ccyy.
Ah. Well, I am not usually a stickler for constraints and data validation, etc., but Dates and Times are my one exception.
In my experience, any Date/Time column that allows invalid values *will* contain invalid values. I am not sure why date/time data is so much more susceptible to this than any other type (though I suspect it has to do with how many different forms and formats they have and how many different ways there are to convert them from one datatype to another), but they most definitely are.
And unlike most other invalid-data incidents, I have always found that this one would have been far easier to fix on the front-end, than on the back-end. So my recommendation has always been to put Dates/Times in Datetime type fields.
All IMPO, of course. 🙂
1) Who said I allow invalid data into my integer-stored date columns?
2) Are you saying that integer-stored (ccyymmdd) date values are improper for data warehousing fact tables? I have seen numerous references and gotten very good results personally using such. This edge is diminished with SQL 2008 and the new date datatype, but most SQL users aren't on that and most apps haven't yet been coded to work with it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 13, 2010 at 12:52 pm
TheSQLGuru (4/13/2010)
RBarryYoung (4/13/2010)
TheSQLGuru (4/13/2010)
RBarryYoung (4/13/2010)
TheSQLGuru (4/13/2010)
Why bother converting the int to date at all? Every human can read and understand 20100411 is April 11 2010. 🙂 And if you need a 'pretty' date, do it on the front-end side...OK, I'll bite. I admit that I cannot tell if you are joking here or not? :unsure:
Not really. I have some data warehousing customers that do present ints such as this directly on output, and some that format the value in the report itself to something like mm/dd/ccyy.
Ah. Well, I am not usually a stickler for constraints and data validation, etc., but Dates and Times are my one exception.
In my experience, any Date/Time column that allows invalid values *will* contain invalid values. I am not sure why date/time data is so much more susceptible to this than any other type (though I suspect it has to do with how many different forms and formats they have and how many different ways there are to convert them from one datatype to another), but they most definitely are.
And unlike most other invalid-data incidents, I have always found that this one would have been far easier to fix on the front-end, than on the back-end. So my recommendation has always been to put Dates/Times in Datetime type fields.
All IMPO, of course. 🙂
1) Who said I allow invalid data into my integer-stored date columns?
I was not addressing you or your databases Kevin. I was addressing everyone else who might read this thread for advice, and your suggestion to them that they store Date information in non-Date datatypes. Specifically, I was presenting an alternative professional opinion and recommendation as to why they should not do this, along with supporting rationale. Sorry if this was not clear.
2) Are you saying that integer-stored (ccyymmdd) date values are improper for data warehousing fact tables? ...
Well, technically, yes it is incorrect, as formally it's a domain specification error in the data design and thus represents an integrity hole. However, it's formal correctness or incorrectness was not really the point that I was trying to make. As I said, I do not normally get hung up on constraint and integrity formalisms...
... I have seen numerous references and gotten very good results personally using such. This edge is diminished with SQL 2008 and the new date datatype, but most SQL users aren't on that and most apps haven't yet been coded to work with it.
I am not disputing that because that also was not my point.
My point was simply that unlike most other data contents, Dates are so easily and frequently mangled and misconverted that any Date-content column that is not a Date-datatype, eventually will get invalid Date data in it.
And further that the fallout (cost of bad/failed results, plus cost of cleanup) for these kinds of cases generally exceed the cost of just specifying the datatype correctly in the first place (this is not always true of other cases).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 14, 2010 at 4:29 am
I'm suprised no one has suggested viewing the query plan for the query in question.
For example it might have made the IN subselect fix more obvious.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply