Help to convert PostgreSQL dates into SQL Server dates

Question

Hello I'm doing some data conversion from PostgreSQL to Microsoft SQL Server. So far it has all went well and I almost have the entire database dump script running. There is only one thing that is now messed up: dates.

The dates are dumped to a string format. These are two example formats I've seen so far: '2008-01-14 12:00:00' and the more precise '2010-04-09 12:23:45.26525'

I would like a regex (or set of regexs) that I could run so that will replace these with SQL Server compatible dates. Anyone know how I can do that?

Answer 1

The first is compatible with datetime, but the second is too precise. It will fit in sqldatetime2, which is available from SQL Server 2008:

select cast('2008-01-14 12:00:00' as datetime)
,      cast('2010-04-09 12:23:45.26525' as datetime2)

For an earlier version, you can use substring to chop off the unstorable precision:

select cast(substring('2010-04-09 12:23:45.26525',1,23) as datetime)

For a regex to remove any additional digits (using perl regex syntax):

(\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}.\d{3})\d*

And replace with:

$1

Which is matches the regex part between () brackets.