CODEWITHSUNDEEP
sqlsql-serversql-server-2008
osyan
osyan

Reputation: 1856

colon(:) and dot(.) as millisecond separator in datetime2

I have migrated a Sybase database to SQL server 2008. The main application that using the database trying to set some of dateTime2 column with data like 1986-12-24 16:56:57:81000 which is giving this error:

Conversion failed when converting date and/or time from character string.

Running the same query using dot(.) instead of colon(:) as millisecond separator like 1986-12-24 16:56:57.81000 or limiting the milliseconds to 3 digits like 1986-12-24 16:56:57:810 will solve the problem.

NOTE:
1- I don't have access to the source of application to fix this issue and there are lots of table with the same problem.
2. Application connect to database using ODBC connection.

Is there any fast forwarding solution or should i write lots of triggers on all tables to fix it using the above solutions?
Thanks in advance

Upvotes: 0

Views: 1100

Answers (3)

osyan
osyan

Reputation: 1856

AS Gordon Linoff said

A trigger on the current table is not going to help because the type conversion happens before the trigger is called. Think of how the trigger works: the data is available in a "protorow".

But There is a simple answer!
Using SQL Server Native Client Connection instead of basic SQL Server ODBC connection handle everything.

Note:
1. As i used SQL Server 2008 version 10 of SQL server native client works fine but not the version 11 (it's for SQL Server 2012).
2. Use Regional Settings make some other conversion problem so don't use it if you don't need it.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269763

Why would you need triggers? You can use update to change the last ':' to '.':

update t
    set col = stuff(col, 20, 1, '.');

You also mistakenly describe the column as datetime2. That uses an internal date/time format. Your column is clearly a string.

EDIT:

I think I misinterpreted the question (assuming the data is already in a table). Bring the data into staging tables and do the conversion in another step.

A trigger on the current table is not going to help because the type conversion happens before the trigger is called. Think of how the trigger works: the data is available in a "protorow".

You could get a trigger to work by creating views and building a trigger on a view, but that is even worse. Perhaps the simplest solution would be:

  • Change the name and data type of the column so it contains a string.
  • Add a computed column that converts the value to datetime2.

Upvotes: 0

krunal modi
krunal modi

Reputation: 135

Select REPLACE(getdate(), ':', '.')

But it will Give String Formate to datetime Which is not covert into DateTime formate

Upvotes: 0

Related Questions