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

    Pergunta


  • 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?
    sexta-feira, 12 de agosto de 2005 23:25

Respostas

  • 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.

    domingo, 24 de dezembro de 2006 01:00
  • 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.
    quarta-feira, 7 de junho de 2006 10:24
    Moderador

Todas as Respostas

  • 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.
    sábado, 13 de agosto de 2005 10:25
  • When I use a stored procedure to insert a datetime value from .net, it still works, so I won't bother anymore.
    sábado, 13 de agosto de 2005 11:25
  •  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
    terça-feira, 16 de agosto de 2005 10:09
  •  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") & ")"
    terça-feira, 16 de agosto de 2005 16:59
  • 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 ...

    sexta-feira, 3 de março de 2006 09:13
  • try something like : DateValue("youDateTimeString here") when inserting or updating
    terça-feira, 6 de junho de 2006 11:54
  • 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.
    quarta-feira, 7 de junho de 2006 10:24
    Moderador
  • 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;


    • Sugerido como Resposta Dev aspnet sexta-feira, 23 de julho de 2010 15:15
    sábado, 23 de dezembro de 2006 12:37
  • 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.

    domingo, 24 de dezembro de 2006 01:00
  • 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..

    terça-feira, 6 de fevereiro de 2007 03:49
  • 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

    quarta-feira, 7 de fevereiro de 2007 11:30
    Moderador
  • 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


    quarta-feira, 15 de agosto de 2007 08:45
  • "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.

    quarta-feira, 12 de dezembro de 2007 15:53
  • 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

     

    quarta-feira, 26 de março de 2008 06:30
  • 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

    quarta-feira, 2 de abril de 2008 12:15
  • 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!!!!!!!

     

     

    quarta-feira, 2 de abril de 2008 12:46
  • 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.

    quarta-feira, 2 de abril de 2008 14:05
  • Maybe you are looking for this solution:

     

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

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

     

    terça-feira, 15 de abril de 2008 10:03
  •  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.
    terça-feira, 19 de agosto de 2008 02:01
  •  

    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

    quinta-feira, 18 de setembro de 2008 10:04
  • Try Adding ...

    SET DATEFORMAT dmy

    ...at the top of your sql. This works for me whenever I come accross this issue.
    • Sugerido como Resposta bPratik terça-feira, 23 de fevereiro de 2010 16:01
    sexta-feira, 7 de novembro de 2008 17:07
  • 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
    quinta-feira, 20 de novembro de 2008 09:15
  • 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 ^_^
    quinta-feira, 18 de dezembro de 2008 08:45
  • 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
    quinta-feira, 18 de dezembro de 2008 10:56
    Moderador
  • 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
    terça-feira, 16 de junho de 2009 05:29
  • 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

    segunda-feira, 14 de setembro de 2009 07:22
  • 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 .
    segunda-feira, 14 de setembro de 2009 09:58
  • Thank you, mahyar2:
    I applied your suggestion (default language='Italiano' for me)
    with full satisfaction

    Giorgio
    segunda-feira, 15 de fevereiro de 2010 13:00
  • 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

    • Sugerido como Resposta bPratik quarta-feira, 21 de julho de 2010 11:13
    terça-feira, 23 de fevereiro de 2010 16:06
  • 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

    quinta-feira, 25 de março de 2010 10:48
  • 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
    sexta-feira, 26 de março de 2010 09:59
    Moderador
  • Reset or restore the system default settings for numbers, currency, time and date. You might accidentally changed date and time settings
    • Sugerido como Resposta Joel Puro segunda-feira, 14 de junho de 2010 08:43
    segunda-feira, 14 de junho de 2010 08:20
  • Reset or restore the system default settings for numbers, currency, time and date. You might accidentally changed date and time settings
    • Sugerido como Resposta Joel Puro segunda-feira, 14 de junho de 2010 08:47
    segunda-feira, 14 de junho de 2010 08:46
  • 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

    quarta-feira, 16 de março de 2011 13:37
  • Gracias,

    Martín Inga

    sexta-feira, 15 de abril de 2011 23:18
  • 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.


    • Editado Markus3000 quarta-feira, 12 de outubro de 2011 17:28
    quarta-feira, 12 de outubro de 2011 17:26
  • woops.. later in the table actually the date strings change their formatting to dd/mm/yy..

    nearly 2 million rows though :p

    quarta-feira, 12 de outubro de 2011 17:51
  • Thank you so much!
    It's works fine for me.
    Greetings from Mexico!
    terça-feira, 16 de julho de 2013 14:53