September 2007 - Posts

Converting to datetime

Converting to a datetime can be a dangerous game. If the month is specified as a word then the date format is unambiguous but very often the year, month and day are all numeric. In this case the conversion can easily be misinterpretted if done in the wrong way.

Until recently I'd thought that the YYYY-MM-DD date format was foolproof, regardless of regional differences:

SET @date = CAST('2003-04-01' AS DATETIME)

Or just

SET @date = '2003-04-01'

I discovered this week that this format is really no better than any other. A new user was created with the default language of their server login set to "British English" rather than "English", and the above conversion generated a date of 4th Jan rather than 1st Apr.

It seems that British English defaults to a date format of ydm, whereas English defaults to ymd. SET DATEFORMAT can be used to control this in some situations, but if you're doing a conversion in a function then this won't work.

A much better, and totally unambiguous, way of creating the date is to use CONVERT. I've used this for years when formatting datetime values as strings but never the other way round.

Using CONVERT it's possible to specify the exact format you're using. I've chosen 112 (YYYYMMDD) as the cleanest:

SET @date = CONVERT(DATETIME, '20030401', 112)

Visual SourceSafe: Resetting working folders
Setting a working folder for a VSS project is pretty standard practice. You set it, and you expect it to cascade down to the project's files and sub-projects.

If a sub-project has a working folder defined that differs from the cascaded one, then this takes priority for that sub-project and anything further down that branch of the tree.

Suppose you want to remove a sub-project's working folder, so it falls in line with the working folder cascaded down from the top level project. You would probably delete the path in the VSS dialog, and the working folders would then be consistent with the SourceSafe database tree.

Now suppose you want to remove a number of sub-project working folders under a certain root project. There might be 10, there might be 100's. The obvious way would be to write a little program that can work through your SourceSafe database and remove the working folders.

According to the documentation on VSS, it is possible to programatically change the working folder for a project in VSS using the LocalSpec property of the IVSSItem interface. It is also supposed to be possible to clear this property, by setting it to a blank string. It isn't.

Working folders are stored on a per user, per project, per machine basis. They are stored in the <VSSDB>\users\<user>\ folder, in a file called ss.ini.

Open this file in a text editor. In VSS, set a project's working folder and over-ride the cascading by setting a sub-project's working folder as well. Close VSS so that the changes to the ss.ini file are saved.

In the ss.ini file you'll notice some new lines at the end, looking something like this:



The working folder for all items (files) and projects under the $/Projects root will default to a subfolder of C:\Projects, unless the item falls under $/Projects/ProjectX, in which case the working folder will be in a separate directory structure under C:\ProjectX.

If you now clear ProjectX's working folder in VSS (and close VSS), the ss.ini file will be update to look like this:



Now everything under $/Projects will have a working folder under C:\Projects.

However, if you'd made the change using the IVSSItem.LocalSpec, then the ss.ini file would look like this:


Dir (PCNAME) =

The effect of this last line is horrible. It stops the cascading of working folders and ProjectX and anything at a lower level have "No working folder" set. If you ever wanted to check out, get or compare, you would have to set a working folder, even though the project fits into the standard tree structure. According to Microsoft, the behaviour is by design!

This problem affects SourceSafe TypeLib 6.0 and 8.0.

Rather than writing an app using the VSS API, I ended up changing my program to edit the ss.ini file directly, removing the lines for the working folders I wanted to remove.
The YearMonth - a simple User-Defined Type

Dealing with data that relates to a particular year and month can cause problems in a database.

You can store it as a datetime but then you have to decide whether to use the first of the month or the last.  If you use the last then you need to work out how many days the month has every time you create a new row.  You also have to remember whether you chose the first or the last - getting this wrong can be disasterous.  And what if you want a unique constraint?  You can easily check whether the date is unique but if someone uses the time component or uses 30 Jul when someone else has used 31 Jul then you've got more problems.

A better solution would be to store it as two numbers (i.e. Year and Month columns).  This will avoid any confusion with times or days but what happens if you need all to return of the data later than Feb 2005?  You can't just query for Year > 2005 and Month > 2 - you'd never get anything for Jan.  If you use (Year * 100) + Month > 200502 then the SQL is simple but you can forget about using any indexes.

The best solution I've seen so far is to use a user-defined type (UDT).

UDTs are not without their problems .  The main disadvantage is that changing a UDT once it's used on a table can be time-consuming, especially if the type is widely used. Additionally, if you're using temporary tables, table variables or multiple servers then there are additional steps required to use UDTs.

One of the best books I've read on SQL Server is Pro SQL Server 2005 Database Design and Optimization by Louis Davidson .  He's not a fan of UDTs, but unfortunately my copy of the book was destroyed in a flood so I can't quote his reasons at the moment.

Having said that, I still think UDTs are the best solution to this problem.

To create the type itself, we use the following syntax:

EXEC sp_addtype YearMonth, int

In SQL Server 2005 or 2008 you can use the alternative syntax, which gives you the option of specifying the schema:


At this point the YearMonth is simply a synonym for an int.  What we need are some rules to define our type:

    @YearMonth BETWEEN 190000 AND 300000


The final step is to bind the rules to the UDT:

EXEC sp_bindrule 'CheckYMYear', 'YearMonth'
EXEC sp_bindrule 'CheckYMMonth', 'YearMonth'

Now we're ready to use the type in a table: 

    TestTableID int NOT NULL,
    TestYearMonth YearMonth NOT NULL

Finally, the following two functions are useful if you ever need to convert between DateTime and YearMonth types:

CREATE FUNCTION fcGetYearMonthFromDate
    @Date DATETIME
@ResultYearMonth YearMonth

    SET @ResultYearMonth = YEAR(@Date) * 100 + MONTH(@Date)

    RETURN @ResultYearMonth

CREATE FUNCTION fcGetDateFromYearMonth
    @YearMonth YearMonth,
    @EndOfMonth BIT
@ResultDate DATETIME
@YearMonthString CHAR(6)

    SET @YearMonthString = CAST(@YearMonth AS CHAR)   

    SET @ResultDate = CONVERT(DATETIME, @YearMonthString + '01', 112)   

    IF @EndOfMonth = 1
        SET @ResultDate = DATEADD(DAY, -1, DATEADD(MONTH, 1, @ResultDate))

    RETURN @ResultDate

Recent Queries

Have you ever needed to write some SQL that's almost the same as a previous query, but the previous query has gone? How many times have you wished you could get it back?!

Unless you save every single modification of every single line of SQL you ever write, then it would be immensely useful to have access to recent queries you've executed.  TOAD has done this for years so I was very disappointed to discover that SQL Server 2005 didn't add this feature and distraught when I found that 2008 doesn't either.

I got so fed-up waiting I looked into writing an add-in to do this, but there's no support for SSMS add-ins in 2005 or 2008. People have done it but there's very little code or documentation out there.  I spoke to a friend at RedGate but he said they couldn't help me as they'd have to divulge what they consider their IP.

Looking on Connect I found a closed article referring to the feature as "T-SQL Recall", which was linked to a live article calling it "Query Execution History". This second one doesn't describe the requirement very clearly, but it boils down to the same thing. (If you can't see these articles then trying logging out of Connect - for some reason I could only read them when I wasn't logged in)

If you like the idea of a recent queries feature giving access to, say, the last 50 chunks of SQL you've executed, and allowing pasting into a query window, then please vote for it.

You might also want to vote for SSMS Add-In support while you're at it - wouldn't it be great if you could customise SSMS the same way you can customise Visual Studio?



Since writing this post I've built a Recent Queries add-in called Total SQL Recall.

If you're interested in writing an SSMS add-in then these may be of interest: