Temp Table Data Types

Question

Post reply

Temp Table Data Types

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

ralm Hall of Fame Points: 3156 More actions · Answer 1

Nice Question.

SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#TempQoD%'

^{[font="Verdana"]Regards,
Rals[/font].}

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 2

Edit: Items in bold

There seems to be some confusion around what is happening here (or I misread some of the comments and the QotD answer?).

The data types of the columns are determined by the expressions in the SELECT statement only.

The INTO clause has no impact other than creating the table based on the resulting data types of the SELECT clause.

datatype for '15 Jan 2013' is simple, but for completeness:

SELECT SQL_VARIANT_PROPERTY('15 Jan 2013', 'BaseType') ExpressionDataType

varchar

The inferred datatype for {d '2012-11-15'} is:

SELECT SQL_VARIANT_PROPERTY({d '2012-11-15'}, 'BaseType') ExpressionDataType

datetime

Best Regards,

Chris Büttner

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

Nice question, learned something about escape sequences. Not that I'll ever use them 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

David Conn SSCertifiable Points: 6010 More actions · Answer 4

After having read Tom's explanation on why it ended up as DateTime. This at least made sense to me.

I would like to think that in the future Microsoft could put a bit more effort into this and actually recognise a Date or a Time data type and not keep making everything DateTime.

baabhu SSCertifiable Points: 6205 More actions · Answer 5

baabhu

SSCertifiable

Points: 6205

April 12, 2013 at 1:37 am

#1605872

Nice learning. Thank you.

sqlnaive SSCoach Points: 17437 More actions · Answer 6

It was tricky to me but i guessed right. Great point to note.

Arno Kwetters Hall of Fame Points: 3733 More actions · Answer 7

rals (3/15/2013)
SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME LIKE '#TempQoD%'

That is even better, Rals. Thanks