Sql-Server

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

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 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 try to allow strings in my applications to use all the richness of the available character sets. If someone has an accent in their name, I really want to repeat it back that way. But I’ve been asked many times if there’s a simple way to strip out all the accents, graves, etc. in T-SQL. These are called diacritics, and we’ve created a function to help you remove them if you decide you really must. (But as I said, really consider keeping them).

2021-01-07

I often need to create a list of columns for a table. Sometimes it’s because I’m generating scripts but there are other times that I need it for dynamic code.

Let’s use WideWorldImporters as a sample. Imagine I need to get a list of columns for the Sales.Orders table.

The wrong way

I’ve seen people doing this the wrong way. They try to use a SELECT statement that joins a variable to itself like this:

2021-01-05

The next set of introductory T-SQL topics that I want to talk about is how to change data and/or data types. The first issue is dealing with NULL values.

Once you get your head around the idea that NULL means the lack of a value (NULL isn’t a value), you might need to replace NULL values with something else. While you could just do that with a CASE statement, T-SQL provides two built-in functions for doing this.

2021-01-04