Help to convert PostgreSQL dates into SQL Server dates
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-1开发者_高级运维4 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?
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.
精彩评论