locked
convert(datetime,'1600-12-31 04:00:00',120) sql server throws out of range RRS feed

  • Question

  • Hi

    Can any one tell why the statement in sql

    convert(datetime,'1600-12-31 04:00:00',120)

     throws out of range exception and how to avoid it?


    Thanks and Regards, Siva Kumar Balaguru

    Friday, March 29, 2013 4:16 PM

Answers

  • I think it's because of date range of the datetime datatype. However, you may try with datetime2 datatype

    SELECT convert(datetime,'1600-12-31 04:00:00' ,120) -- Fail
    SELECT convert(datetime,'1752-01-01 00:00:00'  ,120) -- Fail
    SELECT convert(datetime,'1752-12-31 23:59:59'  ,120) -- Fail
    SELECT convert(datetime,'1753-01-01 00:00:00' ,120) -- Pass
    
    SELECT convert(datetime2,'1600-12-31 04:00:00' ,120) -- Pass


    Narsimha

    • Proposed as answer by Stan210 Friday, March 29, 2013 4:35 PM
    • Marked as answer by Siva Kumar Balaguru Wednesday, April 3, 2013 10:26 AM
    Friday, March 29, 2013 4:29 PM
  • worth noting that datetime2 is available in sql 2008 and above version

    Hope it Helps!!

    Friday, March 29, 2013 5:00 PM

All replies

  • AFAIK, sql server datetime lower limit is 1753 year, you can store it as a date though(sql 2008 and above)...1753 has to do with some gregorian calender or some thing like that. below should work..

    select convert (varchar(20),'1600-12-31 04:00:00',120)

    or

    select convert (date,'1600-12-31 04:00:00',120) -- this gets just the date format and works in sql2008 and above.

    Hope it Helps!!


    • Edited by Stan210 Friday, March 29, 2013 4:29 PM
    • Proposed as answer by Naomi N Friday, March 29, 2013 5:13 PM
    Friday, March 29, 2013 4:26 PM
  • I think it's because of date range of the datetime datatype. However, you may try with datetime2 datatype

    SELECT convert(datetime,'1600-12-31 04:00:00' ,120) -- Fail
    SELECT convert(datetime,'1752-01-01 00:00:00'  ,120) -- Fail
    SELECT convert(datetime,'1752-12-31 23:59:59'  ,120) -- Fail
    SELECT convert(datetime,'1753-01-01 00:00:00' ,120) -- Pass
    
    SELECT convert(datetime2,'1600-12-31 04:00:00' ,120) -- Pass


    Narsimha

    • Proposed as answer by Stan210 Friday, March 29, 2013 4:35 PM
    • Marked as answer by Siva Kumar Balaguru Wednesday, April 3, 2013 10:26 AM
    Friday, March 29, 2013 4:29 PM
  • worth noting that datetime2 is available in sql 2008 and above version

    Hope it Helps!!

    Friday, March 29, 2013 5:00 PM
  • Upgrade to 2012 and use CAST ('1600-12-31 04:00:00' AS DATETIME2(0)). Stop using the old CONVERT string function; the only display format in ANSI Standard SQL is ISO-8601. You might want to look at the direction MS took in teh DATE, TIME and DASTETIME2(n) data types. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Kalman Toth Friday, March 29, 2013 5:32 PM
    Friday, March 29, 2013 5:01 PM