SYSDATETIME() instead of GETDATE() as default value for datetime2 columns
When generating an insert script with default values for a table, use SYSDATETIME() instead of GETDATE() for datetime2 columns. GETDATE(), is fine for datetime columns, but for datetime2 columns, which have a larger precision SYSDATETIME() would be more appropriate.

This is included in SQL Prompt 7 which you can download from http://www.red-gate.com/products/sql-development/sql-prompt/
-
Bruno commented
Just installed the beta - datetim2 columns now indeed have SYSDATETIME() as default when generating insert statements.
Thanks Aaron! -
Timothy Eichman commented
Thanks Aaron--just got the beta and confirms SYSDATETIMEOFFSET works perfectly! Thanks for the quick response.
-
Timothy Eichman commented
With global replication, we've adopted the DATETIMEOFFSET data type: it should script out SYSDATETIMEOFFSET for fields of that type...
-
Thomas Franz commented
it depends :-)
I prefer to use datetime2(2) (= 1/100 sec accuracy) for the most time (e.g. created_on-fields) because it is sufficient and smaller (6 bytes instead of 8).
Of course - if you are recording sensor datas with 10.000 rows per seconds you should use the full datetime2(8) with SysDateTime()