July 9, 2009 at 9:55 am
Anyone have trouble working with DateTime2 fields when moving data between SQL08 and SQL05?
I have a SQL05 server with the following table:
CREATE TABLE [dbo].[CodeTable05](
[PKID] INT NULL,
[Value] [varchar](10) NULL,
[Description] [varchar](250) NULL,
[StartDate] [datetime] NULL,
[EndDate] [datetime] NULL,
) ON [PRIMARY]
I have a linked SQL08 server with the following table:
CREATE TABLE [dbo].[CodeTable08](
[PKID] INT NULL,
[Value] [varchar](10) NULL,
[Description] [varchar](250) NULL,
[StartDate] [datetime2(7)] NULL,
[EndDate] [datetime2(7)] NULL,
) ON [PRIMARY]
I try executing the following script:
INSERT INTO [dbo].[CodeTable05](
[PKID]
[Value],
[Description],
[StartDate],
[EndDate],
)
SELECT
[PKID]
[Value],
[Description],
[StartDate],
[EndDate],
FROM
[MyLinked08Server].[MyDB].dbo.[CodeTable08]
And I get the following error:
Conversion failed when converting datetime from character string.
I tried using CAST and/or CONVERT in my SELECT to get the date into mm/dd/yyyy format, but it didnt' help.
As a test, I tried using the ISDATE() test to make sure I was dealing with recognizeable DATETIME values, but it always returned 0...implying SQL05 didn't even see DATETIME2(7) as a valid date.
Finally, out of pure frustration, I tried SUBSTRING(StartDate,1,10) and viola. But that feels soooo wrong.
Any advice?
July 9, 2009 at 10:18 am
I'd look at building a view over the code table in SQL Server 2008 that casts the dates from a datetime2 value to a datetime value, then use that view to load the data into the table on SQL Server 2005.
July 9, 2009 at 12:35 pm
Yeah, it seems like some kind of mediator is needed. I don't have any control over the SQL08 DB so I need to do something on the SQL05 end. The table only has < 5k records so I thuoght about doing something with a table (temp table, stage table, table variable, cte, etc) on the SQL05 end....I tried a table variable during troubleshooting, but realized the same problem exists...i.e. how to accurately and effectively convert that SQL08 datetime2(7) data into SQL05 datetime data on the SQL05-side.
This article was interesting in understanding the new datetime info...
http://www.sqlservercentral.com/articles/News/3253/
...but it didn't really help me solve my problem. It all boils down to having the ability, in SQL05, to correctly interpret the datetime2 values...hopefully SUBSTRING(1,10,mydatetime2) is not the only option.
July 9, 2009 at 12:49 pm
Problem is that datetime2 is not a recognized data type in SQL Server 2005. That pushes the conversion to SQL Server 2008 side.
July 9, 2009 at 12:52 pm
As Lynn Pettis advised, you need to cast the datetime2 datatype into a datetime datatype but this conversion of datatypes needs to performed on the SQL Server 2008, which supports the conversion, and not on the 2005, which does not support such a conversion.
See the parts of the SQL statements in bold:
INSERT INTO [dbo].[CodeTable05](
[PKID]
[Value],
[Description],
[StartDate],
[EndDate],
)
SELECT
[PKID]
[Value],
[Description],
CAST([StartDate] as datetime ) as StartDate ,
CAST([EndDate] as datetime ) as EndDate
FROM
[MyLinked08Server].[MyDB].dbo.[CodeTable08]
[/quote]
SQL = Scarcely Qualifies as a Language
July 9, 2009 at 2:38 pm
Makes sense...I was just hoping there was some sort of elegant kludge (is that an oxymoron) to make sense of the data on the SQL05 side.
Hmmm....maybe I could do a pass-through (OpenQuery) to the SQL08 server so that the conversion could be done before sending it to the 05 server?
July 15, 2009 at 6:10 pm
FYI: Yeah, a passthrough query seemed to work:
INSERT INTO [dbo].[CodeTable05](
[PKID]
, [Value]
, [Description]
, [StartDate]
, [EndDate]
)
SELECT * FROM OPENQUERY([MyLinked08Server],
'SELECT
[PKID]
, [Value]
, [Description]
, CAST([StartDate] AS DATETIME)
, CAST([EndDate] AS DATETIME)
FROM [MyDB].dbo.[CodeTable08]')
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply