CODEWITHSUNDEEP
sqlsql-serverdatabase
GibboK
GibboK

Reputation: 73918

Best practice Date and Time registration

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. If you are able to send me some link-resource I would appreciate it

Thanks guys :-)

Upvotes: 3

Views: 1821

Answers (3)

Fiona - myaccessible.website
Fiona - myaccessible.website

Reputation: 14921

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!

Upvotes: 0

Oded
Oded

Reputation: 499002

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.

Upvotes: 6

StuartLC
StuartLC

Reputation: 107247

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

Upvotes: 3

Related Questions