Syntax error converting datetime

[mapfax]

Have tried using CAST and CONVERT unsuccessfully, keep getting the same error 'syntax error converting datetime from a character string'.

I am passing dates between a trigger to a stored procedure. The dates are being passed as nvarchar and are converted to a string char(20) in the input parameters of the procedure. If I set the input parameter instead to a datetime it fails the conversion at the input parameter and gives a slightly different error.

I tested the input and they are in the format { FEB 13, 2005 12:00AM } but I just don't seem able to convert them after being passed into the procedure. E.g. { CONVERT(datetime, @date, 103) } or { CAST(@date as datetime) }. I have tried using { SET DATEFORMAT dmy }, but this makes no difference either.

Any ideas? ps sorry I haven't included the code as I am at home, will try and update this later.

 

[JamesLean]

I think we will need to see your code as it sounds like the problem could be in the way you are passing the data between procs and/or converting it between types.

I have tested it simply with:

DECLARE @d char(20)
SET @d = 'FEB 13, 2005 12:00AM'
SELECT CAST(@d AS datetime)

and it works fine. My initial thought is that maybe some values are coming in that are longer than 20 characters and are therefore being truncated, meaning the resulting value is an invalid date format.

But as I say, I can't tell exactly without seeing the actual code.

--James

 

[mapfax]

Thanks James

I checked and double checked my datatype assignments and now it is working.