The Bit Bucket

Sometimes we need to determine whether a string is a date or whether it is a number.

In the first example above, I’m asking if the string ‘20190229’ is a valid date. You can see from the response (0) that it isn’t. That’s because even though it’s a valid date format, February in 2019 doesn’t have a 29th day. It’s not a leap year.

The value returned from the ISDATE function is a zero or a 1.  Curiously, the return value is of data type int. You’d think that a function that starts with Is and tests something would return a bit data type instead. But that’s just one of the curiosities of T-SQL.

2021-02-01

I’ve had a lot of good feedback about the options that we’ve provided in our free SDU Tools for developers and DBAs for formatting strings. Someone recently asked for a format with all capitals and dashes in between. I realised we didn’t have that, and we’ve added it. Generally, in the industry, this is referred to as Cobol Case.

So, we added a function CobolCase.

It takes one parameter:

@InputString - the string to be formatted

2021-01-29

Another book that I listened to recently, also fits into the “I nearly didn’t get past the first chapter” category. It was Atomic Habits by James Clear. By the end of this book though, I realised just how much I’d enjoyed it.

James makes a really interesting study into habits. Far more than anything I’d ever read before.

He makes it so clear how the compound effect of hundreds of small decisions leads to profound life changes. That was already pretty obvious to me but I loved the way he made this so practical. He calls the habits that are formed Atomic Habits.

2021-01-28

I recently recorded a podcast with Kamil Nowinsky. Kamil is well-known for creating some tools that make it easy to publish individual Azure Data Factory pipelines. It’s a far better method that the standard ARM (Azure Resource Manager) template deployment that’s provided by Microsoft.

However, if you use this method (and I recommend you do), you’ll be publishing pipelines separate to the linked services that your datasets connect to.

One mistake I’ve seen a few clients making lately, is that when they created the linked services in different environments, they did so with slightly different names. Because of that, they needed to modify the deployed objects after deployment. You don’t want to be doing that.

2021-01-27

We all use IN when writing queries:

SELECT ProductID, ProductName, Color, Size 
FROM dbo.Products 
WHERE Size IN ('370ml', '220ml');

A challenge comes up though, when you want to find pairs of values using IN. For example, if I have the following pairs of values:

Size        Color  370ml   Blue 370ml   Red 220ml   Blue

How do I find those when using IN?

Other databases do allow you to have pairs:

SELECT ProductID, ProductName, Color, Size
FROM dbo.Products 
WHERE (Size, Color) 
    IN (('370ml', 'Blue'), ('370ml', 'Red'), ('220ml', 'Blue'));

I wish T-SQL had that option but it doesn’t. It would be particularly useful when those values in the IN clause are coming from a sub-query.

2021-01-26

If you are using a string that was provided by another system, it might not be in the format that you were hoping it would be. So for example, if I have this string that says 2/29/2016, I know that’s a US date format, but if it was only 5/4/2016, there’s no way I could just easily tell that.

Conversion functions use your session settings to determine how to convert the values. When I’m converting strings to dates (and/or times), I certainly don’t want to change my settings so that I run with US configuration.

2021-01-25

I spend a lot of time doing data conversion work. It’s hard to imagine how much of that relates to working with dates and times. A requirement that I had recently was to find the nearest weekday to a given date i.e. find me the closest Thursday to a supplied date.

The NearestWeekday function in our free SDU Tools for developers and DBAs, does this.

It takes two parameters:

@TargetDate date - the date that we’re aiming for @DayOfWeek int - Sunday = 1, Monday = 2, Tuesday = 3, etc. (values from 1 to 7)

2021-01-22

I haven’t posted any book reviews lately but I have been getting through quite a few books, so it’s time to rectify that. First up is Gary Vee’s book #AskGaryVee: One Entrepreneur’s Take on Leadership, Social Media, and Self-Awareness. I often read or listen to books that cover entrepreneurship. If you’ve read any of these, or listened to any related podcasts, you will have heard of Gary Vee. Gary is a Belarusian-American whose name is Gary Vaynerchuk.

2021-01-21

Louis Davidson is an old friend of mine. He’s an awesome Data Platform MVP, a database architect and a prolific author. Louis was on an earlier podcast and I was so pleased to invite him back on the show.

SQL Down Under show 81 features Louis discussing his new book, technical writing, relational DB design, and graph DB applications. Louis’ new book is this huge 1154 page volume:

You’ll find the podcast with Louis (and all our other podcasts) here: SQL Down Under Podcast

2021-01-19

I wish SQL Server had more metadata associated with columns in tables, and with objects in general. The closest thing we have to that is the use of extended properties.

Other Databases

In other databases that I’ve worked with, there are richer properties held for each column. For example in Progress, as well as the normal data type and nullability, there were options like:

• Formatting mask (how this column is normally formatted)
• Prompt (what question to automatically ask the user - gives you a chance to explain what you’re asking)

and much more. This type of thing drastically cut down the amount of time it took to build applications using the database, and provided a great level of consistency.

2021-01-19