November 10, 2011 at 7:57 am
You're not running SQL Server 2008. DATE didn't exist pre 2008.
November 10, 2011 at 7:57 am
Very nice question Bit, forced me to do some research. 😀
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 10, 2011 at 7:58 am
jswedlund (11/10/2011)
When I run this I get an error (which was my answer BTW):Msg 243, Level 16, State 1, Line 5
Type DATE is not a defined system type.
Why is this working for everyone else?
Are you running something lower than SQL Server 2008? DATE is not a defined system type until 2008, prior to that it was datetime
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
November 10, 2011 at 8:13 am
I am running SQL Server 2008 R2.
November 10, 2011 at 8:24 am
Sorry, I was connected to a 2005 database when the error message occurred. I restarted without connecting to that db and the query executed correctly.
November 10, 2011 at 8:26 am
Probably using database version other than 2008.
November 10, 2011 at 8:38 am
Nice question.
November 10, 2011 at 9:57 am
Excellent question! I learned a lot today.
Nils, thank you for the sql_variant_property.
Nils Gustav Stråbø (11/10/2011)
select *,sql_variant_property(val,'BaseType') from #test order by val
I had to add a few more inserts into the table in order to understand the sort. (I got it right just by knowing how the dates would sort, but still was unclear about all the other data types.)
CREATE TABLE #tableA(colA SQL_VARIANT, colB INT)
GO
DECLARE @date AS DATETIME
SET @date = '2011-08-27 16:20:28.047'
INSERT INTO #tableA VALUES (CAST(1.1 AS DECIMAL(10,2)),0)
INSERT INTO #tableA VALUES (CAST(46279.1 AS DECIMAL(10,2)),1)
INSERT INTO #tableA VALUES (CAST(900 AS INT),0)
INSERT INTO #tableA VALUES (CAST(-2 AS DECIMAL(10,2)),0)
INSERT INTO #tableA VALUES (CAST(@date AS DATETIME),5)
INSERT INTO #tableA VALUES (CAST(@date AS DATE),2)
INSERT INTO #tableA VALUES (CAST('1776-07-04' AS DATETIME),0)
INSERT INTO #tableA VALUES (CAST(@date AS SMALLDATETIME),3)
INSERT INTO #tableA VALUES ('abc',4)
INSERT INTO #tableA VALUES (CAST('a' AS CHAR(3)),0)
INSERT INTO #tableA VALUES (CAST('zyx' AS CHAR(3)),0)
SELECT colB, colA, sql_variant_property(colA,'BaseType')
FROM #tableA
ORDER BY colA
[font="Courier New"]Results:
colB colA dtype
0 a char
4 abc varchar
0 zyx char
0 -2.00 decimal
0 1.10 decimal
0 900 int
1 46279.10 decimal
0 1776-07-04 00:00:00.000 datetime
2 2011-08-27 00:00:00.000 date
3 2011-08-27 16:20:00.000 smalldatetime
5 2011-08-27 16:20:28.047 datetime[/font]
November 10, 2011 at 12:14 pm
Thanks for the question and thanks Carla for illustrating the additional datatypes
November 10, 2011 at 12:19 pm
Nice question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 10, 2011 at 12:44 pm
I can't believe I spent as much time as I did on this question. Let me share...
I opened the SQL_VARIANT BOL page that describes sorting an families (http://msdn.microsoft.com/en-us/library/ms181071.aspx), because I don't keep the list in my head...wrote the table's colA values and types down on a piece of paper and matched them to the column B values.
Of the types in the table, the [date & time] family sorts highest, then [exact numeric], then the [Unicode] family. Of the [date & time] values, DATETIME will sort highest because SMALLDATETIME is rounded (down), and DATE rounds down to the date only, so the sub-order here is DATETIME highest, SMALLDATETIME, then DATE lowest. The sort order is therefore:
DATETIME - 1
SMALLDATETIME - 2
DATE - 3
DECIMAL - 4
CHAR - 5
Mapping that sort order to the colB values gives the order (in colB terms):
5, 3, 2, 1, 4
BUT the ORDER BY clause is implicitly ASCending, and that list is sorted highest to lowest, so we need to reverse it to get the correct answer:
4, 1, 2, 3, 5
Terrible question, and horrible answer. Thanks, I guess 😛
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 10, 2011 at 12:52 pm
SQL Kiwi (11/10/2011)
I can't believe I spent as much time as I did on this question.
Pretty much what I did (see my post earlier in the thread). I now know there are two of us who would have had the wrong answer if 5,3,2,1,4 was one of the options. It's nice to have company... 😉
November 10, 2011 at 1:40 pm
brazumich (11/10/2011)
SQL Kiwi (11/10/2011)
I can't believe I spent as much time as I did on this question.Pretty much what I did (see my post earlier in the thread). I now know there are two of us who would have had the wrong answer if 5,3,2,1,4 was one of the options. It's nice to have company... 😉
Make that 3 🙂
But I am blaming a late night & a few beers...
November 10, 2011 at 2:08 pm
November 10, 2011 at 2:16 pm
SQL Kiwi (11/10/2011)
I can't believe I spent as much time as I did on this question. Let me share....
. Portions of the original post by SQL Kiwi deleted by this poster
.
Terrible question, and horrible answer. Thanks, I guess :
Since it caused you to take time to answer, in my humble opinion it was a GREAT question .. particulary since I have followed many of your forum postings and learned a great deal from them, it is I who thanks you for being an outstanding contributor here on SSC.
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply