This is my first try at using OpenXML. I am trying to insert a datetime
value with a timezone into a SQL 2000 table datetime column, but I get the
error: Syntax error converting datetime from character string.
I have narrowed the problem down to the inclusion of the timezone, but I am
not able to change this in the xml. There was a post on this problem on
12/22/2005, but for SQL 2005, but I have not been able to make the solution
work for me, so it may be a 2005 enhancement.
What is the best way to handle this?
Here is my code:
DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
SET @.xmlString =
'<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></r
oot>'
EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
SELECT * FROM OpenXML(@.iTree, 'root',2)
WITH (Received datetime 'ReceivedDateTime')
EXEC sp_xml_removedocument @.iTree
<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></ro
ot>The 12/22 solution is indeed a SQL Server 2005 solution.
Since the SQL Server datetime type does not support timezones, the default
casting does not support it.
You have to extract it as a varchar(), use SUBSTRING to drop the timezone
(write your own logic to adjust the value to a normalized timezone first, if
relative order is important) and then cast it to datetime.
Best regards
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
> This is my first try at using OpenXML. I am trying to insert a datetime
> value with a timezone into a SQL 2000 table datetime column, but I get the
> error: Syntax error converting datetime from character string.
> I have narrowed the problem down to the inclusion of the timezone, but I
> am
> not able to change this in the xml. There was a post on this problem on
> 12/22/2005, but for SQL 2005, but I have not been able to make the
> solution
> work for me, so it may be a 2005 enhancement.
> What is the best way to handle this?
> Here is my code:
> DECLARE @.iTree INTEGER, @.xmlString VARCHAR(8000)
> SET @.xmlString =
> '<root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime><
/root>'
> EXEC sp_xml_preparedocument @.iTree OUTPUT, @.xmlString
> SELECT * FROM OpenXML(@.iTree, 'root',2)
> WITH (Received datetime 'ReceivedDateTime')
> EXEC sp_xml_removedocument @.iTree
> <root><ReceivedDateTime>2005-12-13T11:21:26.000-05:00</ReceivedDateTime></
root>
>|||Thanks for the quick response. It was exactly what I needed to know. I hav
e
a few other things that I could do easily with a transform (xslt) that would
make the SQL import easier, and I will probably go ahead with that since thi
s
was is also a problem. But, in case I need this again: is there any way to
make that change to the time (SUBSTRINGing the timezone out) within the
OpenXML statement? I did try to eliminate the zone like:
Received datetime left('ReceivedDateTime', 23)
which was clumsy and did not work, but seemed like it had a chance.
"Michael Rys [MSFT]" wrote:
> The 12/22 solution is indeed a SQL Server 2005 solution.
> Since the SQL Server datetime type does not support timezones, the default
> casting does not support it.
> You have to extract it as a varchar(), use SUBSTRING to drop the timezone
> (write your own logic to adjust the value to a normalized timezone first,
if
> relative order is important) and then cast it to datetime.
> Best regards
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:091651E2-3C18-4FCA-92B4-616A2F12CB99@.microsoft.com...
>
>|||Here is a sample. Note that you can inline the TSQL function into the select
clause directly. You may also want to add some more complex logic to adjust
the date time based on the timezone if you expect more than one timezone to
be provided:
create function RemoveTZ(@.ds as nvarchar(40))
returns nvarchar(40)
begin
declare @.newds nvarchar(40)
if CHARINDEX(N'Z', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
else if CHARINDEX(N'+', @.ds) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
set @.newds =
SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
else -- assume it has no TZ
set @.newds = @.ds
return @.newds
end
go
declare @.h int;
exec sp_xml_preparedocument @.h output,
N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:00
</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
select CAST(dbo.RemoveTZ(d) as datetime)
from OpenXML(@.h, '/root/d')
with(d nvarchar(40) '.')
exec sp_xml_removedocument @.h
Michael
"Trillium" <Trillium@.discussions.microsoft.com> wrote in message
news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
> Thanks for the quick response. It was exactly what I needed to know. I
> have
> a few other things that I could do easily with a transform (xslt) that
> would
> make the SQL import easier, and I will probably go ahead with that since
> this
> was is also a problem. But, in case I need this again: is there any way
> to
> make that change to the time (SUBSTRINGing the timezone out) within the
> OpenXML statement? I did try to eliminate the zone like:
> Received datetime left('ReceivedDateTime', 23)
> which was clumsy and did not work, but seemed like it had a chance.
> "Michael Rys [MSFT]" wrote:
>|||I was
and trying to put the logic/function in the WITH clause - no
wonder it did not work. Your explanation not only answers the question, bu
t
explains the OpenXML query structure.
THANK you!
"Michael Rys [MSFT]" wrote:
> Here is a sample. Note that you can inline the TSQL function into the sele
ct
> clause directly. You may also want to add some more complex logic to adjus
t
> the date time based on the timezone if you expect more than one timezone t
o
> be provided:
> create function RemoveTZ(@.ds as nvarchar(40))
> returns nvarchar(40)
> begin
> declare @.newds nvarchar(40)
> if CHARINDEX(N'Z', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'Z', @.ds)-1)
> else if CHARINDEX(N'+', @.ds) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'+', @.ds)-1)
> else if CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds)) > 0
> set @.newds =
> SUBSTRING(@.ds, 1, CHARINDEX(N'-', @.ds, CHARINDEX(N'T', @.ds))-1)
> else -- assume it has no TZ
> set @.newds = @.ds
> return @.newds
> end
> go
> declare @.h int;
> exec sp_xml_preparedocument @.h output,
> N'<root><d>2005-12-13T11:21:26.000-05:00</d><d>2005-12-13T11:21:26.000+05:
00</d><d>2005-12-13T11:21:26.000Z</d><d>2005-12-13T11:21:26.000</d></root>'
> select CAST(dbo.RemoveTZ(d) as datetime)
> from OpenXML(@.h, '/root/d')
> with(d nvarchar(40) '.')
> exec sp_xml_removedocument @.h
> Michael
> "Trillium" <Trillium@.discussions.microsoft.com> wrote in message
> news:5ADF3F9D-BF96-4211-9BE0-44E5F27AEB1D@.microsoft.com...
>
>
