Thursday, March 8, 2012

datetime convert grief

Hi, I have a problem when I traverse a table to update another, I have a nvarchar in the first holding some dates - (the only option). Though these need to be converted when the records are copied and updated. This is my code that is returning "Syntax error converting datetime from character string." Is there another way that i could do this ?

Declare @.CardNumber int
Declare @.EmployeeNumber int
Declare @.DutyDate nVarchar
Declare @.StartTime nvarchar
Declare @.DutyConvert datetime
Declare @.StartConvert datetime

Declare rsMyCursor Cursor For Select [Site Card Number],[Employee Number],[Duty Date],[Start Time] FROM tblRosta1 WHERE checking is null
Open rsMyCursor

Fetch Next From rsMyCursor
INTO @.CardNumber, @.EmployeeNumber, @.DutyDate, @.StartTime

While @.@.Fetch_Status = 0

Begin
--Select @.DutyConvert = Convert(datetime, @.DutyDate)
--Select @.StartConvert = Convert(datetime, @.StartTime)

INSERT INTO [tblDuties Repository] ([Site Card Number],[Employee Number], [Duty Date], [Start Time]) Values (@.CardNumber, @.EmployeeNumber, Convert(datetime, @.DutyDate), Convert(datetime, @.StartTime))

print + @.CardNumber
Fetch Next From rsMyCursor
INTO @.CardNumber, @.EmployeeNumber, @.DutyDate, @.StartTime

End

Close rsMyCursor
Deallocate rsMyCursor

Any help would be great.

RingoHi

In the variable declaration mention the size. Secondly give a select from that table for these varchar fields and check whether any non-date values are present.

\joe

No comments:

Post a Comment