SergioKastro SergioKastro - 3 months ago 8
SQL Question

From where should I take the date when saving into my Database?

I have been doing a code review and I come across a code which was setting the date like this:

using (var cmd = new SqlCommand("Item_Update", con))
{
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@ItemId", item.Id));
cmd.Parameters.Add(new SqlParameter("@Active", false));
cmd.Parameters.Add(new SqlParameter("@DeactivatedBy", item.DeactivatedBy));
cmd.Parameters.Add(new SqlParameter("@DeactivatedDt", DateTime.Now));
cmd.ExecuteNonQuery();
}


This code will update an item setting the date and the person when it was deactivated (please be aware here that we set the time like this 'DateTime.Now', the time from the web server when the code is executed)

But at the same time, we have a store procedure which updates the item and that store procedure sets also the DeactivateDt

UPDATE item
SET active = 0,
chdt = GETDATE(),
DeactivatedBy = @ExecUserName,
DeactivatedDt = GETDATE()
FROM item
WHERE id = @itemId;


There is nothing wrong on any of both cases, but I was wondering what is the preferred pattern when it comes to set the dates:

1.Set the date from the code

2.Set the date from the SQL server


Not sure if this is a very interesting question, but as I was not 100% sure what to respond on my code review, I have decided to ask you guys.

Thanks for your help.

Answer

Honestly, it should not matter whether you update the date from app tier or db tier. It could be business requirement also. However maintaining consistency across the application is more important from standardization & auditing point of view. This standardization would help in auditing the application in future, in case of any issues.

However, under this circumstances, given a chance, I would recommend DB layer to update the date automatically with default value as GETDATE(), when a new record is inserted.

Again to maintain consistency while updating, if your requirement is to set the same date to all, you can do something like as follows:

DECLARE @currDate DATETIME;

SET @currDate = GETDATE();

UPDATE table SET date = @currDate;

(Note: above code is untested)