Post reply

cannot convert datetime from character string..

  • July 12, 2008 at 1:43 pm

    #123091

    Hi,

    In #tempdb in one of the field the data is coming as 0 and the datatype for it is vachar..

    I need to insert that data into my production db.. when using the convert(datetime,[filedname) I am getting the following error..

    "Syntax error converting datetime from character string."

    can someone help me to solve this problem

  • July 12, 2008 at 1:53 pm

    #842013

    You can check for a correct date format and insert null if it is not correct.

    Declare @test-2 as varchar(20)

    Set @test-2 = '0'

    Select cast( CASE ISDATE(@test) WHEN 1 THEN @test-2 ELSE NULL END as datetime)

    Set @test-2 = '1/1/2008'

    Select cast( CASE ISDATE(@test) WHEN 1 THEN @test-2 ELSE NULL END as datetime)

  • July 12, 2008 at 3:36 pm

    #842022

    Sorry Ken did not see your post before I shoved my two cents in.

    A varchar '0' is NOT a valid date-time format. Date formats are 'mm-dd-yy' examples; '07-09-08' or '07/08/08' or even 07- 08- 08' or '7 jul 2008'.

    Without further information the single character '0' appears to be bad input data. You can look in Books On Line 'BOL' for a multitude of recognizable date formats which is even more extensive than my few samples.

    In addition please read the reference in my signature block so as to provide enough information for us to help you.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

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