Skip to content
← SQL Prompt

How can we improve SQL Prompt?

SQL Prompt

Categories

JUMP TO ANOTHER FORUM

(thinking…)

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.

3 votes

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close

We’ll send you updates on this idea

Bruno shared this idea  ·   ·  Report…  ·  Admin →

We're glad you're here

Please sign in to leave feedback
Signed in as (Sign out)
Close
Close
Submitting...
An error occurred while saving the comment
  • Bruno commented  ·   ·  Report

    Just installed the beta - datetim2 columns now indeed have SYSDATETIME() as default when generating insert statements.
    Thanks Aaron!

  • Timothy Eichman commented  ·   ·  Report

    Thanks Aaron--just got the beta and confirms SYSDATETIMEOFFSET works perfectly! Thanks for the quick response.

  • Timothy Eichman commented  ·   ·  Report

    With global replication, we've adopted the DATETIMEOFFSET data type: it should script out SYSDATETIMEOFFSET for fields of that type...

  • Thomas Franz commented  ·   ·  Report

    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()