The Bit Bucket

In an earlier post, I showed how you could extract the year, month, day, etc. from dates. But these came back as numbers. So instead of “March”, I got 3 returned.

Sometimes though, I want the name of the period, not the number.

Look at this query:

And you can see what it returns here:

This looks easy, but what if I’m not speaking English? It’s important to understand that functions like DATENAME are affected by the regional/language settings that you are currently using in your session.

2020-03-09

I mentioned recently that I haven’t been doing as much driving in my car lately, so that’s limited the time I’ve had for listening to audio books. But another one that I did complete on Audible recently was Stories I’d Tell in Bars by Jen Lancaster.

I’ve seen comments about the printed version of this book, that say it’s riddled with typos and grammar/spelling errors but fortunately that wasn’t an issue for me as I listened to the audiobook. The narration of that was fine.

2020-03-06

In “Progamming 101”, developers learn that sprinkling hard-coded numbers and strings throughout their code, is a really lousy idea. Yet I regularly see T-SQL code that’s littered with hard-coded numbers and “special” string values. I really wish we could avoid that. It would greatly improve code quality, and improve debugging for procedures.

Take the following code as a really simple example:

SET @NextInterval = @CurrentInterval * 1440;

It’s not at all obvious why the value 1440 is there. In this case, it was the number of minutes in a day. Today, we can do this:

2020-03-05

I often need to calculate a day number in a month. For example, I need to work out the 3rd Tuesday of a month, or the 4th Friday, and so on. To make that easy, in our free SDU Tools for developers and DBAs, we added a tool to do just that. It’s a function called DayNumberOfMonth.

It’s a function that takes four parameters:

@Year is the year (of the month in question) @Month is month in question (as a number within a calendar year i.e. March = 3) @DayOfWeek is an integer with Sunday = 1, Monday = 2, etc. @DayNumber is also an integer for the day number (i.e. 3 for 3rd Monday)

2020-03-04

On our SQL Down Under email list today, someone asked:

My title is DBA but my job is more into SQL Developer, fixing data involved in applications. Do you think if I study Power BI that I can get a better job?

I get asked this sort of question regularly, particularly from traditional DBAs who see their roles disappearing.

The most basic answer is to adapt what you’re doing across to roles that are still in need like data modelling, query performance tuning, DB design in general, etc. However, I wanted to make some more broad recommendations for those considering something more radical.

2020-03-03

In an earlier post, I showed how you can use the YEAR, MONTH, or DAY functions to extract those parts of a date. But what if you want to extract any one of those other intervals?

Well, that’s what the DATEPART function does. It allows us to specify the interval that we want to extract. Look at the following query examples:

And here are the results of that query:

2020-03-02

If you have worked with larger tables in either Power BI or Analysis Services, you’ll know that developing against them is painful. Any time the data needs to be refreshed (which happens often), you spend a lot of time sitting around waiting for it to happen.

Now our buddy Chris Webb proposed a way of getting sampled data during development. That’s pretty good. And fellow MVP Marc Lelijveld also uses a similar method.

2020-02-28

I’ve mentioned before that I really don’t mind working with large tables. They’re only slow to use if you’re looking through all the rows when trying to find the data that you want. But there is still sometimes a valid need to archive data from a main table, across into an archive table. And people often run into problems, particularly blocking problems, when they try to do that.

Let’s start with an example pair of tables (much simplified for clarity):

2020-02-27

The DateDiffNoWeekends tool in our free SDU Tools for developers and DBAs, has been really popular. Another request that we received though, was how to calculate which date a particular numbers week day is. For example, we might want to know which is the 12th week day of the month. So we’ve added a tool to do that. It’s called WeekDayOfMonth.

It’s a function that takes three parameters:

@Year is the year (of the month in question) @Month is month in question (as a number within a calendar year i.e. March = 3) @WeekdayNumber is an integer that gives you the position in the month that you are trying to calculate.

2020-02-26

I work with a lot of different software houses. Microsoft calls these ISVs (Independent Software Vendors). They develop applications that we all use day to day.

When I look at their code, many developers have tried to write database-agnostic code. They might be working with SQL Server today, but want to be able to use Oracle, or Cassandra, etc. without changing code.

As a concept, that sounds great. Why would you want to tie yourself into a specific vendor?

2020-02-25