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:
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.
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
I was really excited today to see details of the upcoming #dataweek. Anything with a name like that already has my interest.
In this case though, there’s a #CreateData event that’s happening as part of #dataweek. And the focus of that looks to be on Azure Synapse Analytics.
I know that many of my readers won’t have spent much time with Azure Synapse Analytics. The product has grown out of Azure SQL Data Warehouse (note not Azure SQL Database) but it’s now much more than just an upgraded Azure SQL DW.
2020-11-25
I was really pleased to get to record a SQL Down Under podcast with an old friend Pedro Lopes recently. Pedro is a Principal Program Manager with Microsoft in the Azure Data SQL Server area.
A year or so back, I had Joe Sack on the show telling us where Intelligent Query processing was heading, and Pedro now fills us in on where it’s got to.
We recorded the show earlier in this month but I couldn’t release it until after the PASS summit where some of the features were announced.
2020-11-23
When I’m working in client sites, I get really concerned when I see personal data not being handled or protected appropriately. And one of the biggest sins that I see is where developers have pretended to be encrypting data, but they really aren’t encrypting it.
I’m sure that looks good for a manager but please, just don’t do this !
When I look at the table definition shown in the main image above, my eye is drawn to the column called EncryptedTFN. In Australia, we refer to our tax numbers as TFNs. They are 11 digits long for most people and should never be stored in plain text in a database. The column should be encrypted.
2020-10-27
I’m pleased to let you know that version 20 of our free SDU Tools for developers and DBAs is now released. It’s all SQL Server and T-SQL goodness.
If you haven’t been using SDU Tools yet, I’d suggest downloading them and taking a look. At the very least, it can help when you’re trying to work out how to code something in T-SQL. You’ll find them here:
https://sdutools.sqldownunder.com
Along with the normal updates to SQL Server versions and builds, we’ve added the following new functions:
2020-10-22
