1

My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own culture.

There are some Date, Time and DateTime columns in database. Should I store them:

  • based on SQL Server Date/Time?
  • based on Windows Date/Time?
  • based on Windows Date/Time converted to UTC?

I think I do not need to look into this globalization part at all as the application is standalone and databases are never going to be shared. Data will stay at same machine only, it will not be moved to central location. So, second option is best suited I guess.

I read this question and its answer. DateTimeOffset looks odd; it is generally used for row versioning. I am not sure if it is needed here. That answer is not even accepted; not sure if it really helped OP.

New contributor
Amit Joshi is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • DateTimeOffset is mostly used for proper dates and times. rowversion is the correct type for row versioning. There used to be a type called timestamp which was used for versioning and, confusingly, had nothing to do with time. It has been replaced by rowversion. – Michael Green Feb 12 at 10:07
  • datetimeoffset is not generally used for row versioning. It's just another way to store a temporal value. I suggest you consider using it since it will provide natural ordering, like UTC, and the values can be displayed in the user's culture. – Dan Guzman Feb 12 at 11:43
  • Thanks @DanGuzman; I will think about it. – Amit Joshi Feb 12 at 13:36
1

With stand-alone systems which will never be merged using the local system clock's time has appeal. There is no need for conversion going into nor coming from the database. What you see is what you get. No matter how you access the DB, whether application, reports or ad hoc query the results need no post-processing.

However .. some places observer daylight saving. This means that once (?) a year there will be an hour-long gap in the data. And once (?) a year there will be an hour-long time reversal where, say, sorting on identity column and sorting on a datetime column will produce different ordering.

Now, we don't know your application. We don't know how important any of this could be, or what other work-arounds you might think up, or whether a crazy hour here or there is a problem or not, or any of the other factors that might come into play. But if it is then UTC or time offset is the way to go.

  • Yes; the crazy hour difference dose matter... thanks for noting it explicitly. So, you are suggesting to store it in UTC format in that case. Right? – Amit Joshi Feb 12 at 13:38
  • 1
    Personally I'd go with datetimeoffset. There is no "format" for UTC time. If any code calls SYSDATETIME or GETDATE instead of SYSUTCDATETIME you just get a date and a time. If the user messes with system settings there's no indication in the data. At least with saved offsets all values can be reverted to a common base post hoc. There will be extra coding, depending on who sees what & how. – Michael Green 2 days ago

Your Answer

Amit Joshi is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.