The Bit Bucket

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

In a previous post about CAST and CONVERT, I mentioned that when you use CAST, you don’t get the option to specify the format you want to use. SQL Server offers an extension to the SQL standard with CONVERT and it lets you do just that.

CONVERT has an option parameter that lets you apply a style to the data type conversion that you’re doing.

In the example shown above, I’m converting the current date and time (based upon the SYSDATETIME() function) to a varchar(8), using style 112.

2021-01-18

I do a lot of data conversion work each month. Many systems store date and time values in Unix format, so I often need to convert to/from these in T-SQL. Fortunately it’s easy.

A common way that Unix systems store the date and time value, is to store the number of seconds since the beginning of 1970.

The DateTime2ToUnixTime and UnixTimeToDateTime2 functions in our free SDU Tools for developers and DBAs, does this.

2021-01-15

I’ve seen many challenges with analytics over the years. One that’s painful is an inability to explain where analytic data came from. Someone looks at a report, sees a value, and says I don’t believe that number. Don’t put yourself in that position !

Lineage

I load analytics from data warehouses. Most of my data warehouses are SQL Server databases of some type. Currently, they’re almost always Azure SQL Databases. I like to include information in the database, about how the data got there i.e. the lineage of the data.

2021-01-13

I still get really excited when T-SQL has new language elements added. I’m not talking about new data definition or management statements. I’m talking about new functions and logic. So, as expected, I was really happy when I saw some posts saying that GREATEST and LEAST now seemed to be working in Azure SQL Database.

They’ve been in other database engines before but weren’t part of T-SQL.

I tried them, and sure enough, they work as expected. NOTE: Intellisense hasn’t caught up yet, so they’re still flagged as syntax errors.

2021-01-12

The rich set of data types in SQL Server and the T-SQL language are great. But sometimes, you need to change a value from one data type to another. The output from the commands above is:

CAST

ANSI SQL provided a way to do this.

CAST is the ANSI SQL way to convert from one data type to another. The syntax is basically:

CAST(ValueToConvert AS NewDataType)

And you can use it where you would have used an expression or constant.

2021-01-11

I love the way that tools like Power BI have made creating analytics approachable for a much wider range of users. The skills acquired when doing this are a great start for using analytics in enterprises, but an area that I still see missing is data modelling.

What I see users doing is this:

• Decide what the output reports, etc. should look like
• Design tables that match the reports.

And this is where things go wrong.

2021-01-07