StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>1) Why you get that error: <em>"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value"</em> ?</p> <p>The reason is explained <a href="http://technet.microsoft.com/en-us/library/ms187819.aspx" rel="nofollow">here</a>:</p> <blockquote> <p>Supported String Literal Formats for datetime </p> <p>Numeric [0]4/15/[19]96 -- (mdy), ..., 15/[0]4/[19]96 -- (dmy), ... </p> <p>Description The setting for SET DATEFORMAT determines how date values are interpreted.</p> </blockquote> <p>So, this string literal <code>27/12/2013 8:53:00 PM</code> will be interpreted as DATETIME value using DATEFORMAT setting.</p> <p>Examples:</p> <pre><code>DECLARE @CurrentDateFormat NVARCHAR(3); SELECT @CurrentDateFormat = s.date_format FROM sys.dm_exec_sessions s WHERE s.session_id = @@SPID; PRINT 'Test #1:' SET DATEFORMAT DMY; DECLARE @a DATETIME; SET @a = '27/12/2013 8:53:00 PM'; PRINT @a; PRINT 'End of Test #1'; PRINT 'Test #2:' SET DATEFORMAT MDY; DECLARE @b DATETIME; SET @b = '27/12/2013 8:53:00 PM'; PRINT @b; PRINT 'End of Test #2'; SET DATEFORMAT @CurrentDateFormat; </code></pre> <p>Output:</p> <pre><code>Test #1: Dec 27 2013 8:53PM End of Test #1 Test #2: Msg 242, Level 16, State 3, Line 14 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. End of Test #2 </code></pre> <p>2) Solutions ? Use a string literal format which is <strong>independent</strong> of DATEFORMAT setting. You could find these formats on the same <a href="http://technet.microsoft.com/en-us/library/ms187819.aspx" rel="nofollow">web page</a>. </p> <p>Example with ODBC format:</p> <pre><code>DECLARE @CurrentDateFormat NVARCHAR(3); SELECT @CurrentDateFormat = s.date_format FROM sys.dm_exec_sessions s WHERE s.session_id = @@SPID; PRINT 'Test #1:' SET DATEFORMAT DMY; DECLARE @a DATETIME; SET @a = {ts '2013-12-27 16:53:00'}; PRINT @a; PRINT 'End of Test #1'; PRINT 'Test #2:' SET DATEFORMAT MDY; DECLARE @b DATETIME; SET @b = {ts '2013-12-27 16:53:00'}; PRINT @b; PRINT 'End of Test #2'; SET DATEFORMAT @CurrentDateFormat; </code></pre> <p>Output:</p> <pre><code>Test #1: Dec 27 2013 4:53PM End of Test #1 Test #2: Dec 27 2013 4:53PM End of Test #2 </code></pre> <p>3) These formats determine only how [string literal] values are interpreted as <code>DATETIME</code> values. The internal format/representation is described <a href="http://books.google.ro/books?id=B83dXtOATAQC&pg=PA162&lpg=PA162&dq=sql+server+datetime+internal+representation&source=bl&ots=zDrSjsPlIu&sig=Vzwe26I-KYGpkBPTuX1KgaSqj3w&hl=en&sa=X&ei=zzq_UsLyM6j8ywOw44KYDw&ved=0CG0Q6AEwBw#v=onepage&q=sql%20server%20datetime%20internal%20representation&f=false" rel="nofollow">here</a>.</p> <p>4) </p> <pre><code>DECLARE @StartDate DATETIME, @EndDate DATETIME SELECT @StartDate = {ts '2013-12-27 08:53:00.000'}, @EndDate = {ts '2012-04-10 09:52:48.000'} SELECT DATEDIFF(day,@StartDate,@EndDate) AS DiffDat </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload