?
Solved

CAST DATETIME2 to DATETIME

Posted on 2009-07-09
11
Medium Priority
?
7,341 Views
Last Modified: 2012-05-07
I am having the worst time with this... lord knows what I'm missing.
v2008,  have a very critical solution driven procedurally across the data layer.  it traverses mutliple servers, basically doing reconcilation on demand.  if/when it is needed, it is business critical.  

recently i began upgrading my v2000 servers to v2008.  I'm about half way there -- so this reconciliation logic traverses both v2000 and v2008.  

one of the ups of v2008, of course, is the improved precision with DATETIME2.   my v2008 databases are using DATETIME2 in the timefield.  in retrospect, I should have left it at DATETIME until all my boxes were upgraded.  then i could have converted.

for the most part, there are no problems with the new datetime datatype.  invisibile to the front end.   but it's becoming a problem with this reconciliation solution.  here's the short story

- i create a discrepancy  (basically, delete a trade)
- i then run my finder proc, it finds it, and attempts to write it into a working table
- that write fails with 'Syntax error converting datetime from character string.'
- the datasource in this particular scenario is v2008, the timefield is DATETIME2
- the working table within which this write fails is DATETIME.  i cannot change it, however, because that box is still v2000.  

i MUST have a workaround like yesterday.  whatever workaround i put into place is only temporary --- it will not be an issue when the upgrade is complete.  the timefield in question will be DATETIME2 across the board.

in between now and then, is there not an easy way to strip those last three digits off my datestring and pump it from v2008 DATETIME2 into v2000 DATETIME?

i want this:   2009-07-09 08:45:00.0000000
to be this:   2009-07-09 08:45:00.000

how the heck hard can that be.  
i tried the code below, it returns properly in the select, but every insert from v2008 DATETIME2 into v2000 DATETIME fails w/the error:
'Syntax error converting datetime from character string.'

seriously important.  any input is greatly appreciated
IF(@start IS NULL)
DECLARE @start2 DATETIME
SELECT @start2 = MAX(timefield) FROM v2008Server.database.dbo.table 
SET @start = @start2

Open in new window

0
Comment
Question by:dbaSQL
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24814925
>> in between now and then, is there not an easy way to strip those last three digits off my datestring and pump it from v2008 DATETIME2 into v2000 DATETIME?

Have you tried converting from Datetime2 to datetime like given below:

CONVERT(DATETIME, datetime2_column, 101)

Hope this would help you truncate the additional values.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24814953
Just note that the range of dates between the two data types is also different aside from increased precision in milliseconds.  Therefore, if you have a date outside the range, the conversion may fail.

Can probably leave off code and use like this BTW:

CONVERT(DATETIME, datetime2_column)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24815079
rrjegan17, this fails w/the same syntax error.
mwvisa1, same failure.

i'm just running this against the v2008 dataset:

SELECT CONVERT(DATETIME, timefield)
FROM v2008Server.database.dbo.table WHERE....

i'm not even trying to insert it right now.  just the select fails.
the select without the convert is fine, of course.  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:dbaSQL
ID: 24815199
i do not want to roll back the DATETIME2 in the v2008 instances.  but if i can't get around this, i don't see another option
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24815291
this works in the select:
select convert(varchar(23),timefield,121)

not sure if it will work in the actual insertion yet

isn't there a better way to do this?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 24815703
That should work as the issue is probably the extra digits of precision on datetime2 aren't allowing it to be parsed correctly.

Try this to verify.  If works, should work on insert too.

select convert(datetime, convert(varchar(23),timefield,121), 121)
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24815857
What does this produce when run on the SQL2000 box?

SELECT TOP 1 timefield, substring(timefield, 1 , 23)
FROM v2008Server.database.dbo.table

If the substring just shows a '2009-07-09 08:45:00.000' format then try that.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24815872
that does work, mwvisa1.  select and insertion
query.... that should be fine whether the source is v2008 and destination is v2000, or the opposite, don't you think?

basically, until they're all upgraded, i want one piece of code to work across the board.  one workaround, i guess.   this should do it, regardless of where they're writing to, don't you think?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24815944
Should work both ways, as you can always do this going the other way to maintain datetime2 on the SQL 2008 side:

select convert(datetime2, convert(varchar(23),timefield,121), 121)
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 24816071
excellent.  very big thank you, mwvisa1.  
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24816150
You are welcome.

Happy coding!

Best regards,
Kevin
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question