Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Temp Table Data Types Expand / Collapse
Author
Message
Posted Wednesday, March 13, 2013 8:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:22 PM
Points: 246, Visits: 213
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.?
Post #1430450
Posted Wednesday, March 13, 2013 9:01 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:41 PM
Points: 1,970, Visits: 276
Great question. I hadn't heard of escape sequences, so learned something today.

Simone
Post #1430454
Posted Wednesday, March 13, 2013 9:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 1:13 PM
Points: 134, Visits: 101
Good one
Post #1430479
Posted Wednesday, March 13, 2013 9:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 10, 2014 5:44 PM
Points: 2,225, Visits: 1,258
Good question and a help in understanding the mind of SQL.



Not all gray hairs are Dinosaurs!
Post #1430488
Posted Wednesday, March 13, 2013 12:10 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:18 PM
Points: 4,245, Visits: 3,324
Really new OotD tack - much thanks!
Post #1430563
Posted Wednesday, March 13, 2013 5:09 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:37 AM
Points: 8,287, Visits: 8,738
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
Post #1430682
Posted Thursday, March 14, 2013 3:11 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:26 AM
Points: 1,658, Visits: 6,002
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...
Post #1430825
Posted Thursday, March 14, 2013 3:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,794, Visits: 8,009
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
Post #1430835
Posted Thursday, March 14, 2013 9:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:46 AM
Points: 3,150, Visits: 1,900
Very nice question. The escape sequence for that ODBC date was new to me.
Post #1431083
Posted Thursday, March 14, 2013 10:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:00 PM
Points: 35,967, Visits: 30,258
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1431351
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse