Syntax error converting datetime from character string

Hi, I urgently need some help
I have written a .NET windows service that processes some XML's and updates some fields in an SQL Server 2000 DB. It works ok on my workstation. But at the Client's site it causes an exception with the following error: "Syntax error converting datetime from character string" I heard it might have something to do with regional settings for the workstation and SQL Server Language Settings but im not sure.
Could somebody please help with this issure?!
Thanks

Hi Igor,

I have a similar issue. In my case I'm sending some XML as the argument to a stored procedure and using sp_xml_preparedocument and OPENXML to handle the xml in the stored proc. And as you've probably found it's only the date that causes an issue. i.e. Something to do with the format dates are written to the xml string by .NET. For instance 1/12/2004 is formatted as "2004-12-01T00:00:00.0000000+08:00".

I haven't played with it that much, but a hack is to perform an operation on the raw XML to set time formatting more to what SQL can deal with. In my case, I don't actually need the time components so I could just use

sXml = Replace(sXml, "T00:00:00.0000000+08:00", "")
or even
sXml = Replace(sXml, "T00:00:00.0000000+08:00", " 00:00:00")

Of course, this is not ideal - especially if you have a lot of data to parse. It would be better if you could tell .NET how to format the dates.

If you have found a better option, will you please let me know?

thanks

Matt

try using the Cast or Convert functions.

The solution that I found to this was that I had the @param name surrounded in apostrophies in the sp_insert statement.

Such as:

INSERT INTO Table(dataName)
VALUES ('@data')

Where it is not supposed to contain the variable delimiter ('):

INSERT INTO Table(dataName)
VALUES (@data)

Putting the ', circumvents any prior converting, as using ' tells SQL to read want is contained in the ' ' as a string.

The regional settings shouldn't really have any effect on the way your page processes, one because you should always perform error control with datatype conversions to insure that you always get the proper data format. Two, because you can not control/dictate what your user's machines are set to, as they may have local restrictions to there settings, admins,ignorance,etc. For my insert, I chose to just use the SQLServer getDate(), instead of sending extra data through the pipe.

I know is is an old thread, but hopefully it will help someone in the future.
ryandoah

Hi,
use this query
Select convert(varchar(20),date_column,xxx) from table_name

xxx range from 101 to 114.

Regards
-Gopi