SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Temp Table Data Types


Temp Table Data Types

Author
Message
Jason Whitish
Jason Whitish
Old Hand
Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)Old Hand (340 reputation)

Group: General Forum Members
Points: 340 Visits: 283
I'm particularly curious about the use of the date escape sequence (e.g. {d '2012-11-15'}).

Is it good practice to use those if you're having to hard code dates in procedures, etc.?
sburcombe
sburcombe
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2250 Visits: 295
Great question. I hadn't heard of escape sequences, so learned something today.

Simone
SathishK
SathishK
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 Visits: 111
Good one
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3400 Visits: 1694
Good question and a help in understanding the mind of SQL.

Not all gray hairs are Dinosaurs!
Revenant
Revenant
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8578 Visits: 4907
Really new OotD tack - much thanks!
Tom Thomson
Tom Thomson
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17412 Visits: 12331
Nice question.

A useful reference that should perhaps have been in the explanation is the BOL datetime page which has a information about the ODBC timestamp literals with their literal_type fields such as the one - {d '2012-11-15'} - used in this QotD. All ODBC timestamp literals represent SQL datetime values, even when the literal_type specifies "date only" (as in today's example) or "time only". These are the only datetime literals recognised by T-SQL. As far as I know T-SQL recognises no date literals, no time literals, and no datetime2 literals, so it is not possible to create a column which has any of those types by using select into with literal constant values.

Tom

Toreador
Toreador
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3114 Visits: 8088
sknox (3/13/2013)
It then goes on to provide several data type examples, including a lot of "date" constants which are actually all varchar constants.


Blimey. That really is quite special. Presumably they were trying to say something that was actually true, but I'm struggling to think of anything plausible...
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12148
Toreador (3/14/2013)
sknox (3/13/2013)
It then goes on to provide several data type examples, including a lot of "date" constants which are actually all varchar constants.


Blimey. That really is quite special. Presumably they were trying to say something that was actually true, but I'm struggling to think of anything plausible...


What that page is trying to describe is how implicit conversions of string constants to date are handled. These are very common in real code. For instance, you can have an INSERT or UPDATE with a string constant for a datetime column, or a SET. Or you use a DATEDIFF or DATEADD function with a string constant to represent one of the dates.

All these cases need string constants, because unlike Access, T-SQL doesn't have date constants. (And unfortunately, there are millions of lines of code in the real world where this string constant to date conversion is based on unreliable formats. *sigh*)

EDIT: And the page mentioned by sknox is guilty of that as well. Two examples use December 5th, 1985. The third example suddenly switches to December 5th, 2085. And the last example then introduces May 12th, 2085. At least, that is how I would interpret the given examples. (I am quite sure the author of the page would disagree).
Check http://www.karaszi.com/sqlserver/info_datetime.asp - even though it is quite outdated (the newer date/time types are not covered, as far as I know), most of the information is still very valid.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5481 Visits: 2382
Very nice question. The escape sequence for that ODBC date was new to me.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)SSC Guru (114K reputation)

Group: General Forum Members
Points: 114362 Visits: 41371
Dineshbabu (3/12/2013)
One easiest way to avoid this confusion is .. don't use SELECT * INTO.

Always prefer CREATE TABLE..


Ah, not so fast there. SELECT/INTO is, depending on what your needs are, is an incredibly high performance tool. To adopt a rule of thumb of not using SELECT/INTO is a mistake that can leave you out in the cold performance wise. Like all else in SQL Server, "It Depends".

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search