I answered this question today and thought it would be useful to create a little post

If you have a varchar value like this '2009-06-26 14:30:00.000Z+4:30' you want to take 4 hours and 30 minutes and then subtract that from the date itself so in this case you will get 2009-06-26 11:00:00.000. The reason you subtract is because the +4:30 means that this was generated in a zone that is 4:30 ahead of utc

So first we need to figure out a couple of things

1) where are the minutes?

2) where is the hour?

3) is it positive or negative?

Here are the answers

I answered this question a while back and decided to create a little blogpost.

Let's say you have the following datetime values

2009-05-12 11:13:19.667

2009-05-12 11:12:19.667

2009-05-12 11:33:19.667

2009-05-12 11:43:19.667

2009-05-12 11:03:19.667

2009-05-12 11:53:19.667

2009-05-12 11:53:19.667

2009-05-12 11:23:19.667

2009-05-12 12:13:19.667

2009-05-12 12:12:19.667

2009-05-12 13:33:19.667

2009-05-12 13:43:19.667

2009-05-12 14:03:19.667

2009-05-12 14:53:19.667

2009-05-12 15:53:19.667

200...