We got a question today from a user who wanted to display counts of zero where dates were missing. For example if you had the following data OrderDate SomeCount 2013-01-01 2 2013-01-02 2 2013-01-03 1 2013-01-05 2 2013-01-07 1 2013-01-08 1 2013…

In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code tha…

Function Syntax Return value Return data type DATEFROMPARTS…

SQL Server Denali CTP3 has a bunch of new date/time functions like DATEFROMPARTS, DATETIMEFROMPARTS and EOMONTH First let's take a look at EOMONTH. The syntax for EOMONTH is EOMONTH ( start_date [, month_to_add ] ) If you pass in getdate()…

Someone asked how to change the data type from datetime to datetime2 Someone else answered the following You could add the new column. UPDATE Table SET NewColumn = OldColumn delete the old column then rename the new column. This of cou…

Someone posted a question, they wanted to return the quarters and years within a range that were passed in. The problem they had is that they stored this data in a year and a quarter column. The table looked like this

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