T-SQL help on datetime

• S7

  Mr or Mrs. 500

  Points: 547

  More actions

  January 28, 2014 at 12:11 pm

  #303510

  Getting "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value." error when running the below script:-

  delete from table

  WHERE CONVERT(datetime, cast([year] as varchar(4)) + '-' + cast([month] as varchar(2)) + '-' + cast([day] as varchar(2))) = '?';

  The table without the where clause output is:-

  Year Month Day

  2001 9 14

  2001 9 15

  Any sugesstion on how to twick this query. Thanks.

• Luis Cazares

  SSC Guru

  Points: 183686

  More actions

  January 28, 2014 at 12:28 pm

  #1684032

  I can't reproduce your error. What are you using instead of '?'?

  Can you post DDL of the table?

  Here's what I used to test.

  WITH SomeTable([year],[month],[day]) AS(

  SELECT 2001,9,14 UNION ALL SELECT 2001,9,15)

  SELECT *,CONVERT(datetime, cast([year] as varchar(4))

  + '-' + cast([month] as varchar(2))

  + '-' + cast([day] as varchar(2)) )

  FROM SomeTable

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Kurt W. Zimmerman

  SSCrazy Eights

  Points: 8676

  More actions

  January 28, 2014 at 2:00 pm

  #1684074

  As Luis pointed out the conversion works fine with the sample you have. Maybe there is an instance of data that doesn't contain a valid entry or simply IS out of range:

  Example:

  2014, 2, 29

  Kurt W. Zimmerman
  SR DBA
  Lefrak Organization
  New York, NY

  http://www.linkedin.com/in/kurtwzimmerman

• ScottPletcher

  SSC Guru

  Points: 100262

  More actions

  January 28, 2014 at 5:39 pm

  #1684104

  Don't convert to a datetime, just compare it as characters. Invalid data may still get through, but you can't easily at all correct invalid data on the fly in the query anyway:

  WHERE

  cast([year] as varchar(4)) + '-' + right('0' + cast([month] as varchar(2)), 2) + '-' + right('0' + cast([day] as varchar(2)), 2) = '?';

  SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

• Luis Cazares

  SSC Guru

  Points: 183686

  More actions

  January 28, 2014 at 5:45 pm

  #1684107

  ScottPletcher (1/28/2014)

  ---

  Don't convert to a datetime, just compare it as characters. Invalid data may still get through, but you can't easily at all correct invalid data on the fly in the query anyway:

  WHERE

  cast([year] as varchar(4)) + '-' + right('0' + cast([month] as varchar(2)), 2) + '-' + right('0' + cast([day] as varchar(2)), 2) = '?';

  In that case, she could even compare the columns without concatenation or data conversion.

  WHERE [year] = YEAR(@Date)

  AND [month] = MONTH(@Date)

  AND [day] = DAY(@Date)

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 28, 2014 at 5:46 pm

  #1684108

  I would go one step further and say don't do anything to your year, month and day columns in the WHERE clause.

  Use something like this instead:

  WHERE year=? AND month=? AND day=?

  and pass in the three parameters separately (this is an ODBC query I take it from the use of ? for a parameter...).

  Whatever your method of calling it, you will get better results from not using functions on those columns as long as you have an index that covers them.

  The error will possible be coming from the '?' parameter, not the table's data by the way.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]