none
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime

    Question


  • I am putting the current datetime in a microsoft sql server database in the following manner:


    System.Data.SqlTypes.SqlDateTime time = new        System.Data.SqlTypes.SqlDateTime(DateTime.Now);
    sql = "insert myTable (datetime) values ('" + (string) time.ToSqlString() + "')";
    SqlCommand cmd = new SqlCommand (sql, conn);
    int result =  cmd.ExecuteNonQuery();

    the column of my table has datatype DATETIME


    I tried it 7 times yesterday and today without problem. Now (at 00:45 hours) I get an exception:

    the exceptionmessage:
    System.Data.SqlClient.SqlException: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    I have three questions:

    1. how can the date be out-of-range when I use DateTime as the data type?
    2. why does it works 7 times and not the eight time?
    3. what is the better way to do this?
    Friday, August 12, 2005 11:25 PM

Answers

  • There's only one good way to solve (or rather eliminate) this problem: use parameterized query and forget about formatting issues. Samples can be found on MSDN.

    Sunday, December 24, 2006 1:00 AM
  • It is, actually, a good idea not to use dynamic SQL at all. It is a straight way to the SQL injection attack. Best way is to use parameterized query and provider will handle formatting for all the types automatically. In this case you do not need to convert anything and just assign value to the parameter as is.
    Wednesday, June 07, 2006 10:24 AM
    Moderator

All replies

  • I did an additional test myself;

    I created a table like this:

    CREATE testDateTime
    (dateTime DATETIME)

    and insert:
    insert testDateTime (dateTime)
    values ('13-8-2005 11:56:39')

    this works OK on my local Microsoft SQL Server 2000, but since two days not anymore on my contracters Microsoft SQL Server ("The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value")

    Maybe they had an upgrade with changes in the way datetime values are handled? I sent an email to the administators of my contractors webserver, and I will put a question on a Microsoft SQL Server forum.
    Saturday, August 13, 2005 10:25 AM
  • When I use a stored procedure to insert a datetime value from .net, it still works, so I won't bother anymore.
    Saturday, August 13, 2005 11:25 AM
  •  Jan van Casteren wrote:
    I did an additional test myself;

    but since two days not anymore on my contracters Microsoft SQL Server ("The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value")


    This sounds like a regional setting that has changed

    In the netherlands 13-1-2005 (ddmmyyyy = 13 jan-2005) is a legal date. but when (on the server) the regional setting is changed to English (mmddyyyy) this is not a legal date because there is no 13th month

    Remco
    Tuesday, August 16, 2005 10:09 AM
  •  Jan van Casteren wrote:
    I did an additional test myself;
    ...
    insert testDateTime (dateTime)
    values ('13-8-2005 11:56:39')


    It is a good idea in dynamic SQL to force all dates to a format SQL Server is known to accept regardless of regional setting. I think: yyyy-mm-dd hh:nn:ss, or a subset thereof, is safe. (It's been awhile so double check the format)

    So what you want would be something like this:

    insert testDateTime (dateTime)
    values (" & Format(myDateTime, "yyyy-mm-dd hh:nn:ss") & ")"
    Tuesday, August 16, 2005 4:59 PM
  • I have the same problem :(  only when Update the Database
    When I insert new values, there's no error found !!! 
     

    I use 3 combo box to restrict input dd, mm, yyyy. Then store the result in a variable...  (DateTime type <-- The same the data field Type in SQL 2000.)
    I have checked the System Date Time, the same expected format: dd/mm/yyyy


    I haven't found the answer ...

    Friday, March 03, 2006 9:13 AM
  • try something like : DateValue("youDateTimeString here") when inserting or updating
    Tuesday, June 06, 2006 11:54 AM
  • It is, actually, a good idea not to use dynamic SQL at all. It is a straight way to the SQL injection attack. Best way is to use parameterized query and provider will handle formatting for all the types automatically. In this case you do not need to convert anything and just assign value to the parameter as is.
    Wednesday, June 07, 2006 10:24 AM
    Moderator
  • hi

    this problem maybe solve by changing the default language of login name in security section.
    it must be ENGLISH;

    i have 2 user one query run by SA and another run by PA; query run by PA has this exception and solved;


    • Proposed as answer by Dev aspnet Friday, July 23, 2010 3:15 PM
    Saturday, December 23, 2006 12:37 PM
  • There's only one good way to solve (or rather eliminate) this problem: use parameterized query and forget about formatting issues. Samples can be found on MSDN.

    Sunday, December 24, 2006 1:00 AM
  • Dear Jan van Casteren,

    My client pc also got the same error while inserting data using date picker in the front end VB.net with Sql server 2000 as backend.

    Surprisingly, my system is taking and inserting the values perfectly. I dont know, what could be the reason for this error. can u pls suggest me if u got any solution..

    Tuesday, February 06, 2007 3:49 AM
  • Most likely you have different Regional Settings on these PCs and you are assuming that dates always come in specific format. Do not convert date strings into dates inside of the SQL statements. Take date from the control as date type and pass it to the query as a parameter. Do not concatenate it as a string. It will solve the issue

    Wednesday, February 07, 2007 11:30 AM
    Moderator
  • I dont like parameteres, because i coulndt just cut & Paste my complete statement into the SQL Query box to test it

    I use DateTime.Now.ToString( "yyyy-MM-dd HH:mmTongue Tieds" )  that works. Its ISO


    Wednesday, August 15, 2007 8:45 AM
  • "In the netherlands 13-1-2005 (ddmmyyyy = 13 jan-2005) is a legal date. but when (on the server) the regional setting is changed to English (mmddyyyy) this is not a legal date because there is no 13th month"

     

    Just being pedantic, but (mmddyyyy) format ISN'T English, it's American.  (ddmmyyyy) is English as well as Dutch.  Don't want to be tarred with the same odd brush as those funny americans - don't know why they can't use the same date format as the rest of us.

    Wednesday, December 12, 2007 3:53 PM
  • This also had me stumped for awhile...

     

    Solution is as follows:

     

    string hour = TextBox3.Text;

    string minute = TextBox4.Text;

    string year = Calendar1.SelectedDate.Year.ToString();

    string month = Calendar1.SelectedDate.Month.ToString();

    string day = Calendar1.SelectedDate.Day.ToString();

    string dt = "'" + year + "-" + month + "-" + day + " " + hour + ":" + minute + ":00'";// '2003-12-23 22:12:00' - Note the '     '

     

     

     

    SqlDataSource1.InsertCommand = "INSERT INTO tblData(coNum, coTime) VALUES ("1"," + dt + "')";

     

    Hope it helps Smile

     

    Wednesday, March 26, 2008 6:30 AM
  • Hi there, hope you guys can give me an idea.

     

    I have a corporate table (can't change it) that has a char(10) column with a date value (as string of course). Here is the example of it's content:

    select DT_INI_VIG from T017HIEF;

    1994-12-01
    1995-06-06
    1997-11-07
    1999-05-19
    1999-09-20

    Notice that the strings are formated as yyyy-mm-dd

     

    I created a view (i also need this view, reaaly do) on this corporate table as follows:

    create view my_view as

    SELECT CAST(DT_INI_VIG AS DATETIME) AS COTIND_DAT

    FROM T017HIEF WHERE ISDATE(DT_INI_VIG) = 1;

     

    Here is the situation:

    When I select records from the view, it works just fine, I get all the data converted to datetime:

    select COTIND_DAT from my_view;

    1/12/1994
    6/6/1995
    7/11/1997
    19/5/1999
    20/9/1999

     

    But when I try to do a (this is an example situation, not an unnecessary select)

    select COTIND_DAT from my_view where cotind_dat = '01 apr 2008',

    select COTIND_DAT from my_view where cotind_dat = 'apr 01 2008' or even

    select COTIND_DAT from my_view where cotind_dat = '2008-04-01', I get the

    "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime" error.

    Anyone has any guesses why?

    Thank you all

    Wednesday, April 02, 2008 12:15 PM
  • Hi, just adding to my question, the thing seems to be that SQL Server, instead of resolving the view FIRST, and using the results in my query, it does BOTH things at the same time, damn!

    Forget the view, just help me solve this:

    select CAST(DT_INI_VIG AS DATE1TIME) AS COTIND_DAT
      FROM T017HIEF
     WHERE ISDATE(DT_INI_VIG) = 1 and

    CAST(DT_INI_VIG AS DATETIME) = '01 apr 2008'

    This also doesn't work for the same reason, instead of solving the inner query first, it does BOTH things at the same time:

    select * from (

    select CAST(DT_INI_VIG AS DATETIME) AS COTIND_DAT
      FROM T017HIEF
     WHERE ISDATE(DT_INI_VIG) = 1 ) my view

    where COTIND_DAT = '01 apr 2008'

    The T017HIEF table has invalid dates in the string field DT_INI_VIG, like '2008-02-31', and it's quite hard for me to remove this records, is from a corporate table...

    Thank you!!!!!!!

     

     

    Wednesday, April 02, 2008 12:46 PM
  • Hi guys,

    just to let you know, I was able to solve this using the following code on my subselect (ending up to the view I wanted):

     

    select * from (

    CAST(CASE WHEN ISDATE(DT_INI_VIG) = 1 THEN DT_INI_VIG ELSE '1900-01-01' END AS DATETIME) AS COTIND_DAT

      FROM T017HIEF
     WHERE ISDATE(DT_INI_VIG) = 1) my_view

    where COTIND_DAT = '01 apr 2008'

     

    Thanks.

    Wednesday, April 02, 2008 2:05 PM
  • Maybe you are looking for this solution:

     

    string dateTime = DateTime.Now.ToString("s");

    string sqlStatement = "INSERT INTO dateTimeTest (dateTime) values ("+ dateTime +")";

     

    Tuesday, April 15, 2008 10:03 AM
  •  nfsRagnar wrote:

    Maybe you are looking for this solution:

     

    string dateTime = DateTime.Now.ToString("s");

    string sqlStatement = "INSERT INTO dateTimeTest (dateTime) values ("+ dateTime +")";

     



    thanks nfsRagnar. It's works.
    Tuesday, August 19, 2008 2:01 AM
  •  

    I have written an article related to this,

     

    Please check the below URL,

     

    http://venkattechnicalblog.blogspot.com/2008/09/convert-varchar-to-datetime-in-sql.html

     

    Regards,

    Venkatesan Prabu .J

    Thursday, September 18, 2008 10:04 AM
  • Try Adding ...

    SET DATEFORMAT dmy

    ...at the top of your sql. This works for me whenever I come accross this issue.
    • Proposed as answer by bPratik Tuesday, February 23, 2010 4:01 PM
    Friday, November 07, 2008 5:07 PM
  • I believe this is the perfect answer.I spend good part of my day to sort this problem then found it.
    Use the date in yyyy-mm-dd hh:nnTongue Tieds format.

    Cheers
    Thursday, November 20, 2008 9:15 AM
  • leotijo said:

    I believe this is the perfect answer.I spend good part of my day to sort this problem then found it.
    Use the date in yyyy-mm-dd hh:nnTongue Tieds format.

    Cheers

    Didn't work.

    Trying
    SELECT cast('18.12.2008' AS DateTime) 
    Return
    18.12.2008 - dmy format

    Trying
    INSERT INTO [some_table] VALUES('18.12.2008');
    Return
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Trying
    INSERT INTO [some_table] VALUES('2008-12-18 00:00:00'); 
    Return the same error
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Three days before all was fine. There was no changes in SQL server configuration.

    P.S. Sorry for my bad english ^_^
    Thursday, December 18, 2008 8:45 AM
  • Have you tried to pass date as a parameter? You do not need to do any casting if you pass date value as date datatype using parameter in your code.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, December 18, 2008 10:56 AM
    Moderator
  • VMazur i support your effort in posting. But we get what your saying but we dont want to do it that way. I perfer writing normal sql code that i learned at uni thats an international standard that dosent require compiling and that exposes any issues in underlying software. Its obvious this is a bug in MSSQL so would u please spend your effort talking to ms programmers to fix the bug .. telling us to code things your way is not acceptable. We pay for ms software... we expect ms to fix it.
    GLenn
    Tuesday, June 16, 2009 5:29 AM
  • Hi,

    I converted date to "yyyy-MM-dd" and it worked for me! Because SQL Server can identity the day part & month part easily.

    Raj

    Monday, September 14, 2009 7:22 AM
  • its a problem with your regional settings. you have to convert date according your regional settings date format. see full solution here http://dileepsol.blogspot.com .
    Monday, September 14, 2009 9:58 AM
  • Thank you, mahyar2:
    I applied your suggestion (default language='Italiano' for me)
    with full satisfaction

    Giorgio
    Monday, February 15, 2010 1:00 PM
  • As a quick resolution to the issue, I modified my queries to look:

    "SET DATEFORMAT dmy; SELECT..."

    This sets it for the current execution thereby not breaking any other existing code!

    Thanks @Heresandyboy

    • Proposed as answer by bPratik Wednesday, July 21, 2010 11:13 AM
    Tuesday, February 23, 2010 4:06 PM
  • hallo, bPratik

    this followed in this result

    Msg 2812, Level 16, State 62, Line 1
    Could not find stored procedure 'SET DATEFORMAT dmy; SELECT...'.

    have you any ideas

    thank you

    greetings heatxp

    Thursday, March 25, 2010 10:48 AM
  • What do you have as CommandType specified in a code? Is it StoredProcedure? If yes, you cannot use this option, since your statement does not contain only the name of the stored procedure, but other SQL statements. You should use Text option instead
    Val Mazur (MVP) http://www.xporttools.net
    Friday, March 26, 2010 9:59 AM
    Moderator
  • Reset or restore the system default settings for numbers, currency, time and date. You might accidentally changed date and time settings
    • Proposed as answer by Joel Puro Monday, June 14, 2010 8:43 AM
    Monday, June 14, 2010 8:20 AM
  • Reset or restore the system default settings for numbers, currency, time and date. You might accidentally changed date and time settings
    • Proposed as answer by Joel Puro Monday, June 14, 2010 8:47 AM
    Monday, June 14, 2010 8:46 AM
  • After much going around the houses with this problem, I finally solved it by using this the convert functionality on this website http://sqlusa.com/bestpractices/datetimeconversion/  (WARNING lots of bright colours)

     

    I found that CAST didn't work.

    CODE:

    int intMonth = cal.get(Calendar.MONTH) +1;
                if (intMonth < 10) {
                    month = "0" + intMonth;
                } else {
                    month = "" + intMonth;
                }
                int intDay = cal.get(Calendar.DAY_OF_MONTH);
                if(intDay < 10){
                    day = "0" + intDay;
                } else {
                    day = "" + intDay;
                }

                date = "'" + cal.get(Calendar.YEAR) + "-" + month + "-" + day + " 00:00:00'";

                Statement stmt = con.createStatement();
                sql = " select count(*) from AT_RBA_Holidays "
                        + "where AT_RBA_Holidays.StartTime <= convert(datetime, " + date + ", 120)"
                        + " AND AT_RBA_Holidays.EndTime >= convert(datetime, " + date + ", 120)"; --This is the important bit

    Wednesday, March 16, 2011 1:37 PM
  • Gracias,

    Martín Inga

    Friday, April 15, 2011 11:18 PM
  • I have the same error message, but I can reproduce it in MSSQL 2008R2 server manager (pasting in the SQL):

    SELECT TOP (1) *

    FROM [dbo].[PosLog] AS [t0]

    WHERE ((CONVERT(DateTime,[t0].[RECEIVED])) >= '2009-02-14 00:00:00.000')

    In this table RECEIVED is a varchar(20) that looks like a date strings.

    But the maximum date in the received column is '2009-02-09'.  When I set the date in the query to anything less than this maximum date, I don't get the error.

    But there's nothing wrong with the strings in the RECEIVED field, since the query:

    select max ( CONVERT(DateTime,[RECEIVED]))  from poslog

    runs fine.

    I checked the language settings, it is English.


    • Edited by Markus3000 Wednesday, October 12, 2011 5:28 PM
    Wednesday, October 12, 2011 5:26 PM
  • woops.. later in the table actually the date strings change their formatting to dd/mm/yy..

    nearly 2 million rows though :p

    Wednesday, October 12, 2011 5:51 PM
  • Thank you so much!
    It's works fine for me.
    Greetings from Mexico!
    Tuesday, July 16, 2013 2:53 PM