Best practice Date and Time registration

Question

Hi, I am building a simple CMS to manage articles. My MS SQL Server 2008 is Hosted in USA, Author of Data Base are situated in USA and Germany.

When a Author create an article in the DataBase I would like record the DATE of creation.

I would like to show the date on the website as would all contents and articles are created from Germany.

My questions: A) - shell I use SYSDATETIMEOFFSET() as DEFAULT in DataBase and in the Business Logic Layer converted to Germany time?

B) - shall I add DATE from the Business Logic Layer directly without letting the DataBase adding the datetime, and showing the data as it is?.

I hope my question is clear, because I am quite confuse on the subject. If you are able to send me some link-resource I would appreciate it

Thanks guys :-)

Answer 1

My recommendation would be to go with UTC - this way you have an absolute frame of reference.

This is done in SQL Server using

select getutcdate()

Answer 2

Store dates in UTC with the timezone offset, use this date inside the application as well.

Only convert to the local time display in the last moment.

Best practices for datetime, daylight saving and timezones are collected in this question.

Answer 3

Store dates in some standard time in your DB, regardless of time zone it originated from.

Store/calculate time zone of user, and whenever you display the data to the user, convert it to the user's timezone in the BL/Presentation Layer. Don't do it in the UI, you'll end up with pain later!