How does ADO.NET store dates and time?

Question

I was lazy and write an insert statement with DateTime.Now. It occur to me later i should have written DateTime.Now.ToUniversalTime(). This got me thinking, does ADO automatically convert dates into universal time? and restore it to local when i pull data out? Or do i need a write ToUniversalTime and ToLocalTime myself in every area of code? How should i manage time properly on my site?

Answer 1

The DateTime structure contains the Kind property that specifies if the value is a local time or an universal time (or unspecified).

When you store the date in a database it's only the date and time components that are stored. The Kind component is lost, so when you read the DateTime value from the database you can't tell if it was a local or a universal time that was stored.

I recommend that you store universal time in the database, and also put "UTC" in the field name to make it obvious what's stored in the field, for example CreatedUtcDate.