ISO 8601 and datetime store/retrieve from DB.

[BLUE]

I want to store and retrieve datetime in ISO 8601 format:

'2007-06-02T16:12:08.123-04:34'
or
'2007-06-02T16:12:08.123Z'


When I insert a datetime with SQL Server Management Studio and then I do a
SELECT I loose original "string" and the datetime get converted in a local
datetime.

Suppose I have an handheld in NY and another in Athens, the DB server in
Moscow and the front end application that sees DB data in Rome.
My handheld app stores ISO 8601 datetime into an XML file and send it to the
DB server through a Web Service.

I'd like the user in Rome to see datetimes on a datagrid all with Rome time
zone or with originals timezones or as a string in ISO 8601 format.
How to do that?

I've converted all datetimes to UTC and then stored them without the
trailing Z so that the DB does not convert them.

Can I add a column with the UTC offset to recreate original datetimes or the
overhead before displaying the dataset is too much?


Thanks,
Luigi.

 

[Nicholas Paldino [.NET/C# MVP]]

Luigi,

I don't think you have to do anything of the sort. If you are already
storing the datetimes in UTC in the database, then I would perform the
conversion to local time based on the current system settings. Once you get
the DateTime from the database, you just have to call ToLocalTime on the
DateTime instance to get the UTC time in local time.

 

[BLUE]

I've done some tests founding that System.Datetime can handle well datetime
values (no seconds offset allowed and rounding if more than 7 digits
supplied):

2007-06-06T09:03:01.1234567+02:00


SQL Server instead handle only datetime values of this kind:

2007-06-06T09:03:01.123
2007-06-06T09:03:01.123Z

And both are stored as the same value!


How can I store all datetimes with timezone info?
To make comparisons I've decided to store them as UTC, but how to get back
the original values?

To be clear:

My datetime is 2007-06-06T12:03:01.1234567+02:00
I store it as 2007-06-06T10:03:01.123Z
How can I get back 2007-06-06T12:03:01.123+02:00???

I think this is only possible if I add a string column wich holds the
offsett in this format Shh:mm (with S = + or -) right?


Thanks,
Luigi.