Converting from a string (pulled from xml) to datetime

Question

Post reply

Converting from a string (pulled from xml) to datetime

jenn-654330

SSC Enthusiast

Points: 192
More actions
February 25, 2008 at 2:59 pm

#62744

I am trying to convert a string to date time. The string exists in xml and is loaded from a DateTime field in C#. The string looks as follows: "2008-02-25T12:36:36.1804709-06:00".
when i try to load that string into a DateTime field in a table, i get the following error:
Conversion failed when converting datetime from character string.
The stored procedure that is trying to do this looks like such:
Declare @tblAnswers table
(
ID uniqueidentifier,
QuestionListID uniqueidentifier,
PatientAssessmentID uniqueidentifier,
[Value] varchar(50),
DateEntered DateTime
)
Insert Into @tblAnswers (ID, QuestionListID, PatientAssessmentID, [Value], DateEntered)
Select
T.C.value('@id', 'uniqueidentifier'),
T.C.value('@QuestionListID', 'uniqueidentifier'),
T.C.value('@PatientAssessmentID', 'uniqueidentifier'),
T.C.value('@Value', 'varchar(50)'),
Cast(T.C.value('@DateEntered', 'DateTime') as DateTime)
From @patientAssessmentAnswerXML.nodes('/Root/PatientAssessmentAnswers') as T(C)
I have tried Converting and Casting, but i still get the same error.
Thanks,
Jennifer

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

I think the datetime can't read this statement as date

"2008-02-25T12:36:36.1804709-06:00".

If you want the date only you can use SUBSTRING to get just the first ten (10) values in the statement (giving you only 2008-02-25)

"-=Still Learning=-"

Lester Policarpio

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

In the XML the value you are getting is the datetime with the time zone offset. Is there a way for you to convert that in the C# code to a string before putting it into the XML? It looks like you have a schema that is defining that field in the XML as a datetime and it is messing you up. Or could you pass just the datetime value as a parameter from the C# code? If you can't do that then you will have to parse the date string to get the date and time like Lester suggests.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

jenn-654330 SSC Enthusiast Points: 192 More actions · Answer 3

Thanks, i ended up changing how the datetime was written in the xml. Now the value that is passed in is a recognized date and I don't get any errors.

Thanks again!:)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

The thing that bugs me here is that BOL states that that very format SHOULD work. It's style #127. Is BOL just lying or is there something escaping me on this one?

I mean - even just copying the very example of a style 127 UTC date string out of BOL and trying to convert it fails, utterly. What gives?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 5

There's a style #126 in 2k... but no style #127... at least none that I can see under CONVERT in BOL...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Fair enough - but there is one on SQL 2005, and it don't work either.

126 (4)
ISO8601
yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127(6, 7)
ISO8601 with time zone Z.
yyyy-mm-ddThh:mi:ss.mmmZ
(no spaces)

Just noticed that was a new one. I keep doing that these last few days (spewing out info on the wrong version).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

[qoute]Just noticed that was a new one. I keep doing that these last few days (spewing out info on the wrong version)[/quote]

Yeah and another version coming this fall.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Jack Corbett (2/27/2008)
Yeah and another version coming this fall.

Kick a man while he's down ,heh? (just kidding). It would be nice if those going over BOL would put little tags like "this feature is NEW here" just like the "this feature is deprecated and will be removed in future versions of SQL". It would help a lot. As it now stands - you have to keep multiple BOL windows open and do comparison reads to find these new things.... It especially doesn't help that the "context sensitive help" in 2005 seems to arbitrarily pull from any version of BOL it seems to find, to include 2008.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

jcrawf02 SSC-Insane Points: 24198 More actions · Answer 9

Matt Miller (2/27/2008)
Jack Corbett (2/27/2008)
Yeah and another version coming this fall.
Kick a man while he's down ,heh? (just kidding). It would be nice if those going over BOL would put little tags like "this feature is NEW here" just like the "this feature is deprecated and will be removed in future versions of SQL". It would help a lot. As it now stands - you have to keep multiple BOL windows open and do comparison reads to find these new things.... It especially doesn't help that the "context sensitive help" in 2005 seems to arbitrarily pull from any version of BOL it seems to find, to include 2008.

You could just do what I do, and work for a company that only uses the (now unsupported) SQL Server 2000, to avoid confusion . . . .:blink:

---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 10

jcrawf02 (5/20/2008)
Matt Miller (2/27/2008)
Jack Corbett (2/27/2008)
Yeah and another version coming this fall.
Kick a man while he's down ,heh? (just kidding). It would be nice if those going over BOL would put little tags like "this feature is NEW here" just like the "this feature is deprecated and will be removed in future versions of SQL". It would help a lot. As it now stands - you have to keep multiple BOL windows open and do comparison reads to find these new things.... It especially doesn't help that the "context sensitive help" in 2005 seems to arbitrarily pull from any version of BOL it seems to find, to include 2008.
You could just do what I do, and work for a company that only uses the (now unsupported) SQL Server 2000, to avoid confusion . . . .:blink:

Sometimes it just makes you want to stop pulling your hair out and retreat to the comfortable low-lying level of MS Excel.

Scratch that - I remember trying to fully figure out Excel v.2007: made me feel dumb too. Hey wait - I'm sensing a theme there....:w00t:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?