Temp Table Data Types

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dave62 » Temp Table Data Types

36 posts, Page 4 of 4

«««1 2 3 4

Temp Table Data Types

Rate Topic

Display Mode

Topic Options

Author

Message

rals

Posted Friday, March 15, 2013 12:37 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 1:13 AM
Points: 1,720, Visits: 746

Nice Question.

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

^{Regards,
Rals.}

Post #1431369

Christian Buettner-167247

Posted Friday, March 15, 2013 2:39 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:36 AM
Points: 2,522, Visits: 3,616

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

Post #1431406

Koen Verbeeck

Posted Friday, March 15, 2013 5:35 AM

SSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 6:54 AM
Points: 9,364, Visits: 6,462

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

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1431475

David Conn

Posted Wednesday, March 27, 2013 12:12 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 1,685, Visits: 642

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.

Post #1435777