strings

SQL Server – Why storing dates as strings is a bad idea

Matthew Darwin Database, Dates, SQL, SQL Server, String manipulation, T-SQL , , ,

Consider the following value: ’01-02-10′.  Is it the 1st February 2010?  Is it the 2nd of January 2010?  Is it the 10th of February 2001?

All three of those answers are potentially valid, and you can convert that string into all three:-

DECLARE @DateString CHAR(8) = '01-02-10';

SET DATEFORMAT DMY;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT YMD;

SELECT CONVERT(DATE, @DateString);

SET DATEFORMAT MDY;

SELECT CONVERT(DATE, @DateString);

whichdateisright

71902217

Continue reading →

Replacing SQL Text within a script file using regular expressions and Powershell

Matthew Darwin automation, files, Powershell, scripting, SQL Server , , , , ,

I recently needed to update a load of scripts in our Source Control to remove the filegroup declaration from ON [PRIMARY] to just not be declared, so that new objects would be deployed to the default filegroup on some new databases we had created. Personally, I like to keep user tables away from the system tables to assist with being able to do piecemeal restores, and allow easier management of database files.

From looking at the scripts, written by lots of different devs, there were various different standards and styles that had been applied, from not declaring a filegroup (yey no work for me!), to differing numbers of spaces between the ON and [PRIMARY] keywords:-

  • ON [PRIMARY]
  • ON  [PRIMARY]
  • ON   [PRIMARY]

All in all, there were a few hundred scripts that I needed to make this change to and I had absolutely no intention of doing that manually. So: to Powershell!

Continue reading →