Post reply

Unique Error with convert function in update query

  • June 6, 2012 at 6:29 pm

    #259123

    Hi Friends,

    I am receiving below error when trying to execute an sql stmnt.

    UPDATE test1

    SET

    count = '3'

    WHERE

    fileid = ISNULL(null, fileid) AND

    count= '' AND

    CONVERT(datetime, CreationDate, 112) > GETDATE()

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

    The format of datevalues stored in the "CreationDate" column is "20120604".

    I am receiving this error only on one machine and only with update statement on that machine. In otherwords, if I execute below select statements, they are working fine. Only update statement is not working and I could execute update staement in other machine with same data.

    select * from test1 where

    CONVERT(datetime, CreationDate, 112) > GETDATE()

    or

    select CONVERT(datetime, CreationDate, 112) from test1 where

    CONVERT(datetime, CreationDate, 112) > GETDATE()

    I am tired of working on this issue, any help is much appreciated.

    Thanks,

    Sundeep

  • June 6, 2012 at 9:08 pm

    #1497977

    Please not this query was working fine for long time...issues are popping up all of sudden..

  • June 6, 2012 at 9:59 pm

    #1497980

    sundeep38 (6/6/2012)

    UPDATE test1

    SET

    count = '3'

    WHERE

    fileid = ISNULL(null, fileid) AND

    count= '' AND

    CONVERT(datetime, CreationDate, 112) > GETDATE()

    Does your data follow style 112? (yyyymmdd)

    Can you attach the estimated execution plan for this statement as a *.sqlplan file please?

    Can you provide some sample CreationData data and the table definition?

    Why are you storing 'count' as a string?

    Why are you storing 'CreationDate' as a string?

    What on earth is "fileid = ISNULL(null, fileid)" trying to do?

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • June 6, 2012 at 11:16 pm

    #1497991

    I got the same error yesterday while I was performing an insert on some table. Then I remembered that I had changed the Date Format From 'ymd' To 'mdy'.

    So, I ran the wuery "Set DateFormat ymd" and then the Insert query started working fine.

    Maybe it could work in your case too.

    Vinu Vijayan

    For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

  • June 6, 2012 at 11:42 pm

    #1497995

    Did you try to find the issue in the data using the isdate function ?

    select Datecol, ISDATE(Datecol)

    from yourtable

    where ISDATE(Datecol) = 0

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 5 posts - 1 through 5 (of 5 total)

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