Results 1 to 13 of 13

Thread: Convert Error

  1. #1
    Join Date
    Jan 2005
    Posts
    16

    Unanswered: Convert Error

    I am trying to convert a varchar(10) into a smalldatetime field in a view.

    The table holds the date information in the format dd/mm/yyyy in a
    varchar(10).

    I can convert most fields in this format in my view using;

    CONVERT (smalldatetime, ActualInDate, 103)

    However one field fails, I have some dates as 02/01/0000, this is because it is a blank system date which is output from my mainframe job to extract data from another server.

    Can I convert these blank dates to sql blank dates in my convert statement, ie replace these dates from 02/01/0000 to 01/01/1900.

    The error I get is the error below;

    Database Server: Microsoft SQL Server
    Version 08.00.0760
    Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime datatype resulted in an out-of-range small datetime value.

    Cheers,

    Jon

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess would be that you are having problems with different data rows than you think are causing the problems. Use the IsDate() function to show you the "problem children" in your data. I suspect that your server is interpreting the dates as MM/DD/YYYY instead of dd/mm/yyyy as you expect. If that is the case, I'd convert the whole bunch to ISO standard format of YYYY-MM-DD (like 1900-01-01), which is a lot harder to misinterpret!

    -PatP

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    This appears to work:

    CREATE TABLE MyTable
    (
    ActualInDate varchar(20)
    )

    INSERT INTO MyTable(ActualInDate)
    VALUES ('02/01/0000')
    INSERT INTO MyTable(ActualInDate)
    VALUES ('13/02/1975')

    SELECT CONVERT (smalldatetime, (CASE WHEN ActualInDate = '02/01/0000' THEN '01/01/1900' ELSE ActualInDate END), 103)
    FROM MyTable
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Yup, it appears, but appearances are misleading...

    SELECT CONVERT (smalldatetime, (
    case
    when charindex('0000', ActualInDate) > 0 then '01/01/1900'
    else case
    when isdate(ActualInDate) = 0 then '01/01/1900'
    else ActualInDate end
    end), 103)
    FROM MyTable
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2005
    Posts
    16
    Thanks for the ideas but I just used this to do it and it seems fine:

    CONVERT (smalldatetime, REPLACE(ActualIn, '02/01/0000', '01/01/1900'), 103)

    Cheers

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    rdjabarov,
    do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

    He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Thrasymachus
    rdjabarov,
    do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

    He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.
    I don't think that's his sole purpose in life, but rdjabarov often comes across that way. I've just had to develop thicker skin where his posts are concerned.

    He really does know what he's doing (at least as often as most of us do anyway ), but he comes across pretty abrasively at time.

    -PatP

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well there's the other side of the coin as well...you have to take everything with a grain of salt...because

    IT JUST DOESN'T MATTER

    Speaking of which...where's Rudy?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    I am sorry.

    I did not say he did'nt know what he was talking about. I was objecting to the snide remarks

    I am sorry.
    I am cranky ass.
    Quitting smoking.
    Unrealistic deadline.
    Little sleep.
    Stressed from a couple job interviews.
    Too easily offended from my hot southern celtic blood.
    I apologize.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    Speaking of which...where's Rudy?
    speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by r937
    i was actually enjoying the intellectual dialogue in this thread

    I'm still looking....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937
    speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

    Yeah, but it does help to put things into context!

    -PatP

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat does contexts
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •