September 12, 2006 at 3:52 am
Hi,
I am in the process of converting some XML Schema data to relational , and experiencing problems when converting datetime with timezone data that used the collation SQL_Latin1_General_CP850_BIN as I receive the error:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
The strange thing is that it works ok with SQL_Latin1_General_CP1_CI_AS
Adrian
An example is
Use Master
go
--COLLATE SQL_Latin1_General_CP1_CI_AS
select convert(datetime,'2004-01-11T17:11:03Z')
Use Wallet0000
go
--COLLATE SQL_Latin1_General_CP850_BIN
select convert(datetime,'2004-01-11T17:11:03Z')
Output
-----------------------
2004-01-11 17:11:03.000
(1 row(s) affected)
-----------------------
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
September 12, 2006 at 10:51 pm
DECLARE @D TABLE (CP1_CI_AS varchar(40) COLLATE SQL_Latin1_General_CP1_CI_AS
, CP850_BIN varchar(40) COLLATE SQL_Latin1_General_CP850_BIN)
INSERT INTO @D
SELECT '2004-01-11T17:11:032','2004-01-11T17:11:032'
SELECT * FROM @D
select convert(datetime,REPLACE(CP1_CI_AS,'T',' '))
, convert(datetime,REPLACE(CP850_BIN,'T',' '))
FROM @D
select convert(datetime,CP1_CI_AS)
--, convert(datetime,CP850_BIN)
FROM @D
GO
Looks like SQL 2000 SP4 does not like the ISO8601 date format, no matter the collation?
Your example strings use a Z character instead of the numeric 2, took me a while to see this, damn those old eyes.
Andy
September 13, 2006 at 2:02 am
Andy,
The Z ( For time Zone ) is an addition for 2005, and I suspect due to the XML support which holds date/times with it, so im not suprised is does not with SQL 2000 SP4
Any other ideas welcome
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply