0

We're building a system which has audit fields on every table (CreatedDate, LastUpdateDate), which are currently DATETIME fields.

The values for both are set via the .Net code (Through EF).

Would there be any benefit at all to change them all to DATETIME2?

And...

Without triggers, is there a way that I can set these on Update/Insert on the table? Adding a DEFAULT of GETDATE() to 'CreatedDate' may help for that field (Although, it can still be overwritten by the .Net code)... but what can I do with LastUpdateDate?

Craig
  • 18,074
  • 38
  • 147
  • 248

1 Answers1

0

Ok this question is popular one. Your 1st part answer will be if you're concerned more about compatibility than precision, use datetime. Some clients doesn't support date, time, or datetime2 and force you to convert to a string. So use it wisely. More over datetime2 has a bigger range.

SQL Server datetime2 vs datetime https://technet.microsoft.com/en-us/library/bb677335.aspx http://sqlhints.com/2011/09/17/difference-between-datetime-and-datetime2-datatype/

I'm sorry I can't properly understand second part of your question. If you please clear me.

bluetoothfx
  • 643
  • 9
  • 23
  • Thanks. The 2nd part was more about, 'How can I set the CREATEDATE, and LASTUPDATE when the row is added or modified, without using a trigger. – Craig Mar 30 '16 at 22:31
  • If this problem comes to me then I solve the problem from code end. I used to keep **CreatedDate,CreatedBy,ModifiedDate,ModifiedBy** when I *insert a new entry* I fill the **1st two properties** and second two able to take **null**. After if I update or modify the object then I update **second two properties.** That's all – bluetoothfx Mar 30 '16 at 22:37