I have seen enough questions about this lately and this means that it is time for a blogpost. SQL Server 2008 has a bunch of new data types and one of them is the date datatype.

If you don't care for the time portion of the date you can now use the date data type and save 5 bytes per row compared to datetime. I know that there is smalldatetime which only takes up 4 bytes but I myself could not use that because we have data that goes back to 1896 and thus can't be stored in smalldatetime

So take a look at this code

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...

ISO Week in SQL Server

First let's take a look at what ISO week is, from WikiPedia:

Week date representations are in the format as shown below.

YYYY-Www or YYYYWww

YYYY-Www-D or YYYYWwwD

[YYYY] indicates the so-called ISO year which is slightly different than the calendar year (see below). [Www] is the week number prefixed by the letter 'W', from W01 through W53. [D] is the weekday number, from 1 through 7, beginning with Monday and ending with Sunday. This form is popular in the manufacturing industries.

There are mutually equivalent definitions fo...

Take a look at this query.