Temp Table Data Types

  • ralm

    Hall of Fame

    Points: 3156

    Nice Question.

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

    [font="Verdana"]Regards,
    Rals
    [/font].
  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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: 258965

    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: 5769

    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: 6202

    Nice learning. Thank you.

  • sqlnaive

    SSCoach

    Points: 17435

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

  • Arno Kwetters

    Hall of Fame

    Points: 3377

    rals (3/15/2013)


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

    That is even better, Rals. Thanks

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

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