Post reply

Converting varchar to datetime in select statement

  • June 29, 2006 at 8:13 am

    #646704

    I wouldn't combine data cleansing with ordinary DML. Check the values using a SELECT with ISDATE() first, then fix any corrupt values, then move the data.

    If you have mixed date formats, in particular mixed dmy, mdy formats, then in some cases this will be caught by the ISDATE() which I think only allows US (mdy) format. But there could be other cases in European format which are also valid (though incorrect) US dates. In that case you are in trouble, and will have to do some more detailed checking and possibly manual cleaning.

    The immensely unuseful moral of the story is don't ever store non-string values as strings.

     

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • June 29, 2006 at 8:14 am

    #646706

    In relation to:

    CASE WHEN ISDATE

    (DischargeDate) = 1 THEN DischargeDate END,

    What does the select statement return if ISDATE(DischargeDate) <> 1.  Does it just return nothing for that row?

     

  • June 29, 2006 at 8:17 am

    #646708

    It returns <NULL> value for every ISDATE() = 0. So for all values that can't be interpreted as date [ISDATE() = 1], the CASE statement substitutes the string with <NULL> instead.

     

    N 56°04'39.16"
    E 12°55'05.25"

  • June 29, 2006 at 8:30 am

    #646716

    > ISDATE() which I think only allows US (mdy) format

    I don't think this is correct. I think it works from the dateformat setting (see example below).

    I agree that data cleansing shouldn't be combined with ordinary DML - that is unless the data cleansing is trivial, which might be the case here.

    DECLARE @datestring varchar(8)

    SET @datestring = '12/21/98'

    set dateformat dmy

    SELECT ISDATE(@datestring)

    set dateformat mdy

    SELECT ISDATE(@datestring)

    /*results

               

    -----------

    0

              

    -----------

    1

    */

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • June 29, 2006 at 8:38 am

    #646719

    Yes, wasn't sure and didn't look it up. Largely irrelevant to the issue though.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 5 posts - 16 through 19 (of 19 total)

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