Post reply

Syntax error converting datetime from character string.

  • October 20, 2006 at 8:24 am

    #97883

    Hi,

     

    I’m having some trouble to complete the SQL Query bellow

     

    I get this error :  

    Server: Msg 241, Level 16, State 1, Line 1

    Syntax error converting datetime from character string.

     

    If I remove the datediff function it works fine……

     

    If I do the same thing directly :   select datediff( hh, 'May 17 2002 12:00', 'May 18 2002 15:00') from…

     

    It works fine …..

     

    I don’t understand why ..any idea ?

     

    select processos.processo, processos.dataabertura, processos.horaabertura,

    substring(cast(Processos.Dataabertura as char),1 , 12)+substring(cast(processos.horaabertura as char),1, 5),

    substring(cast(cabecinterv.Datafim as char),1 , 12)+substring(cast(cabecinterv.horafim as char),1, 5)

    ,

    datediff(HH, substring(cast(Processos.Dataabertura as char),1 , 12)+substring(cast(processos.horaabertura as char),1, 5),

    substring(cast(cabecinterv.Datafim as char),1 , 12)+substring(cast(cabecinterv.horafim as char),1, 5))

     FROM Cabecinterv, Processos WHERE (Cabecinterv.Processo=Processos.Processo)

     AND ((NOT Processos.Fechado=0) AND Cabecinterv.Estado IN ('14', '15', '5', '7')

     AND Processos.Cliente NOT  IN ('PRIMAVERA', 'TESTES') AND Processos.Cdu_Uss=0

    AND (NOT Cabecinterv.Tecnico='32328') AND

    Cabecinterv.Tipointerv IN ('12', '14', '2', '4', '6', '9'))

  • October 20, 2006 at 9:21 am

    #667100

    First I would recommend you explicitly specify the length of your cast.

    cast(Processos.Dataabertura as char(12)

    But also just execute this

    Select substring(cast(Processos.Dataabertura as char),1 , 12)+substring(cast(processos.horaabertura as char),1, 5) as date1,

    substring(cast(cabecinterv.Datafim as char),1 , 12)+substring(cast(cabecinterv.horafim as char),1, 5) as Date2

     FROM Cabecinterv, Processos WHERE (Cabecinterv.Processo=Processos.Processo)

     AND ((NOT Processos.Fechado=0) AND Cabecinterv.Estado IN ('14', '15', '5', '7')

     AND Processos.Cliente NOT  IN ('PRIMAVERA', 'TESTES') AND Processos.Cdu_Uss=0

    AND (NOT Cabecinterv.Tecnico='32328') AND

    Cabecinterv.Tipointerv IN ('12', '14', '2', '4', '6', '9'))

    Review the data, Something in there is not working out to be an actual date string, Find it, and Fix it.

  • October 20, 2006 at 9:35 am

    #667107

    Ok , i have corrected the char(12) , nerveless I still get the same error

     

    I agree with you it must be a problematic date in a row because if I select a subrange of the actual selection it works….

     

    But I’m having difficulties to identify the pattern date that is causing the failure

  • October 20, 2006 at 9:45 am

    #667118

    Try This.

    Select substring(cast(Processos.Dataabertura as char),1 , 12)+substring(cast(processos.horaabertura as char),1, 5) as date1,

    substring(cast(cabecinterv.Datafim as char),1 , 12)+substring(cast(cabecinterv.horafim as char),1, 5) as Date2

     FROM Cabecinterv, Processos WHERE (Cabecinterv.Processo=Processos.Processo)

     AND ((NOT Processos.Fechado=0) AND Cabecinterv.Estado IN ('14', '15', '5', '7')

     AND Processos.Cliente NOT  IN ('PRIMAVERA', 'TESTES') AND Processos.Cdu_Uss=0

    AND (NOT Cabecinterv.Tecnico='32328') AND

    Cabecinterv.Tipointerv IN ('12', '14', '2', '4', '6', '9'))

    AND (isdate(substring(cast(Processos.Dataabertura as char),1 , 12)+substring(cast(processos.horaabertura as char),1, 5) ) <> 1

    OR

    isdate( substring(cast(cabecinterv.Datafim as char),1 , 12)+substring(cast(cabecinterv.horafim as char),1, 5) ) <> 1

    )

    Should help U find bad rows

  • October 20, 2006 at 10:57 am

    #667150

    Ok. Thanks!

     

    I have identified the origin of the error, it’s when for example I have an hour like ‘9:53’ instead of ’09:53’  I think  the datediff function is not able to do the match …..

     

    It’s difficult to change manually all the rows that have this characteristic , so … is there any way to extract the hour and the minutes and then match them later (because what I’m doing know its just a substring….)

  • October 20, 2006 at 11:15 am

    #667157

    What is the datatype of the Processos.Dataabertura field and the processos.horaabertura field?

    Also can you post some sample data of what works, and does not work?

     

     

  • October 25, 2006 at 4:37 am

    #667802

    Smalldatetime

  • October 25, 2006 at 5:19 pm

    #668047

    I'm thinking it just might be something else because the following works just fine and without any error...

    select datediff( hh, 'May 17 2002 9:53', 'May 18 2002 9:53')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 8 posts - 1 through 7 (of 7 total)

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