November 29, 2009 at 7:49 pm
I have a webservice that pokes data at a SQL2005, one of the destination fields is a datetime field. The Web Service passes a varchar and SQL2005 accepts it and adds the data, converting to the appropriate datetime format without a single complaint.
When I point the webservice to the SQL2008 version of the database, I get an error - "Error converting data type varchar to datetime."
Any ideas how I might reconfigure SQL2008 to cope with this?
Data trying to be added - '2009-11-30 15:16:43.9270000'
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
November 29, 2009 at 9:38 pm
richardn-1128243 (11/29/2009)
Data trying to be added - '2009-11-30 15:16:43.9270000'
You say, web service passes a varchar(8), and what do you mean by this in the last line.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 30, 2009 at 9:51 pm
No - not a varchar(8) but it passes a varchar (any size, usually 27), specifically this value '2009-11-30 15:16:43.9270000' and that's when SQL2008 gives the error "Error converting data type varchar to datetime."
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
November 30, 2009 at 9:54 pm
No - not a varchar(8) but it passes a varchar (any size, usually 27), specifically this value '2009-11-30 15:16:43.9270000' and that's when SQL2008 gives the error "Error converting data type varchar to datetime."
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
November 30, 2009 at 10:19 pm
richardn-1128243 (11/30/2009)
No - not a varchar(8) but it passes a varchar (any size, usually 27), specifically this value '2009-11-30 15:16:43.9270000' and that's when SQL2008 gives the error "Error converting data type varchar to datetime."
What is the Datatype in SQL Server 2008 where you are inserting the value
'2009-11-30 15:16:43.9270000' ?
If it is a Datetime Column the error is normal, you cannot store that format of Date and time in a column with datatype datetime.
There is another datatype --- [datetime2](7) Your value '2009-11-30 15:16:43.9270000' can be stored in a column with this Datatype.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 12:18 pm
The data tyep is a [datetime], as it was in SQL2005 - I take it that [datetime2] is now the equivalent in SQL2008?
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
December 1, 2009 at 12:34 pm
richardn-1128243 (12/1/2009)
The data tyep is a [datetime], as it was in SQL2005 - I take it that [datetime2] is now the equivalent in SQL2008?
I tested and confirmed that.
You either have to use the [datetime2](7) Datatype or use some sort of trimming, or cast / convert into normal value of datetime (which can only store upto 3 decimals after seconds). Your application is sending the value as '2009-11-30 15:16:43.9270000' the last 4 '0000's have to be removed . trimmed etc..
I have not used that trimming or casting in 2008 as of now. So test it.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 1, 2009 at 2:52 pm
Thanks - I will give that a go.
In which case, I have lots of tables in this DB that need the datatype DATETIME to be upgraded - just as well we are testing first!
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
December 3, 2009 at 1:49 pm
That fixed it!
Had to add a DATETIME2 field, copy the data from the original field, remove indexes that referred to the orig, delete the orig field, rename the new field then recreate the indexes - put it all in a script.
Now I need to do this to all other tables - anybody any great ideas how to shortcut this?
[font="Arial"]-----------------------------------------------------------------
Rich N - Development Manager @ Delta Technology Solutions Ltd - M.O.T.H.E.R. your virtual Sys Admin - Remote Server Health Monitoring - 24 x 7 www.deltatsl.com[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply