Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Need help - Error - The conversion of a char data type to a datetime data type

Options
  • 24-04-2009 4:12pm
    #1
    The Mighty Dubs
    Registered Users Posts: 224 ✭✭


    Hi,

    Im trying to convert a varchar to a datetime and am getting the following error.

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

    One of the problems is that the data in the fields are in the following formats

    Dec 31 2008 12:00AM
    02/17/2009
    20/02/2009
    <null>

    I need to somehow convert all these to
    20/02/2009 format

    Im using MSSQL 2000 btw

    Any help will be most appreciated.

    Thanks


Comments

  • Options
    #2
    Ginger
    Registered Users Posts: 2,931 ✭✭✭Ginger


    You can use an ISNULL statement to get you out of the whole NULL issue.

    Your best bet it is run it through an ASPX file and adjust the logic, otherwse run a stored proc with a cursor and see what happens


  • Options
    #3
    ronivek
    Registered Users Posts: 1,916 ✭✭✭ronivek


    You can also use isdate() to check if a varchar is suitable for conversion in the first place.


  • Options
    #4
    Andrew76
    Moderators, Computer Games Moderators Posts: 10,412 Mod ✭✭✭✭Andrew76


    Hi there,

    To add to the other replies, the following code converts your sample values to DATETIME:
    DECLARE @Str VARCHAR(100)
-- Comment/uncomment to test diff values.
SET @Str = 'Dec 31 2008 12:00AM'
--SET @Str = '02/17/2009'
--SET @Str = '20/02/2009'
--SET @Str = NULL

SELECT @Str [Var], 
CASE WHEN ISDATE(@Str) = 1 THEN CAST(@Str AS DATETIME)
ELSE CONVERT(DATETIME,@Str,103) 
END [Date]

    I needed the convert 103 for the 20/02/2009 value (probably due to my sql language settings). Hope this is of some help.

    Andrew.


Advertisement