Post reply

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

  • February 22, 2006 at 6:45 am

    #112957

    Hi all,

    I have the following statement on my Prodcution server:

    select cast(convert(char(100),pi_dtbirth,103) as datetime)  as [Date] from pi

    This works perfectly.

    However when I try it on the same database on a different server I get:

    Server: Msg 242, Level 16, State 3, Line 1

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

    If I change it to:

    select cast(convert(char(100),pi_dtbirth,101) as datetime)  as [Date] from pi

    it also works OK, but if i try:

    select cast(convert(char(100),pi_dtbirth,101) as datetime)  as [Date] from pi

    on my Production server I get the same error.

    I have checked the regional settings of both machines and as far as I can tell both the SQL Servers are set up and configured the same.

    What am I missing here, other than a few brain cells?

    Regards

    Jon

     

  • February 22, 2006 at 7:53 am

    #622616

    Hi

    Just to narrow the prob down a bit:

    Have you tried with a few hardcoded values e.g.

    select cast(convert(char(100),'1980-03-22',101) as datetime) as [Date]

    Are the dates in the two different DBs all the same?

    What format are they in - mm/dd/yy, dd/mm/yy, yyyy-mm-dd ?

    Are there lots of them in there as it may just be a single entry causing the problem.

    btw - That's my birthdate up there. Cards, money and presents gratefully accepted.

  • February 22, 2006 at 8:15 am

    #622627

    I've tried against several different date fields in several different tables and always get the same result.

    The db is an exact copy, backup + restore.

    I've reduced the data set to try and rule out dodgy data and also just tried against the first record but still get the same.

    select Top 1 cast(cast(convert(char(10),pi_dtbirth,103) as varchar) as datetime)  as [Date] from pi

    The dates are held in YYYY-MM-DD format.

    I'm sure its something simple but just cant work it out. I have put in a work around but don't like knowing why I can't get something to work.

    Cheers

    Jon

    p.s. Well happy birthday in a months time!!!!! Whats the weather like up there in sunny Aberystwyth?

     

  • February 22, 2006 at 8:19 am

    #622630

    Try checking the data with the ISDATE command. That should point out any invalid dates.

    Also, make sure the SQL Server collation is the same on both systems.

    -SQLBill

  • February 22, 2006 at 8:20 am

    #622631

     

    This problem is arise because of the different -2 date formats.

    before converting a character date into datetime ,

    you should also look for the its style.

     

    REGARDS

    AMIT GUPTA

  • February 22, 2006 at 8:28 am

    #622636

    SQLBill - Yes, I have tried checking with ISDATE, I do not beleive it is a problem with the data as such, more of a configuration issue. I have checked the Collation and its the same on both servers and for both database. I have even checked the individual field to make sure it didnt have any different collation setting.

    Amit Gupta - What would make the date format different? I have checked the machine regional settings and the SQL Server and they seem to be configured in the same way.

    Thanks for your input.

    Jon

  • February 22, 2006 at 8:46 am

    #622641

    "Whats the weather like up there in sunny Aberystwyth?"

    Sunny of course (amazingly enough I'm not being sarcastic either).

    Do you want to send me your list of dates and I'll try them on my SQL Server installation? I don't know if I'd be able to spot a collation problem but it might help.

  • February 23, 2006 at 6:53 am

    #622904

    Style 101 is mm/dd/yyyy

    Style 103 is dd/mm/yyyy

    Only one of these will work by default for any given server installation, unless the SET DATEFORMAT statement is used to override it.

    My suggestion is to use style 112 ( yyyymmdd ) which will always work regardless of the regional settings or SET DATEFORMAT state.

     

  • February 23, 2006 at 7:06 am

    #622912

    Check the Language setting for the logins on both servers as this will affect the ways dates are converted unless explicitly specified using SET DATEFORMAT as mkeast stated.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • February 23, 2006 at 7:34 am

    #622925

    Hi All,

    Yep I got it to work, with your help, I had the Server set to British English and the Login set to just English. I did'nt know it was quite that fussy, but anyway its all sorted now.

    Thanks one and all for your input.

    Regards

    Jon

  • February 23, 2006 at 8:06 am

    #622948

    You could also try

    select dateadd(d, datediff(d, '1900', pi_dtbirth), '1900') as [Date] from pi

    (or a number of other possibilities). Then you don't have to worry about language settings

    I wouldn't be surprised if it was faster than your query, but I haven't tested it.

     

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply