You Probably Don’t Need ODBC Functions

This past week I stumbled across an ODBC Scalar Function for the first time. What was this which lay before me? Is that SQL with curly braces?!

It returned the current date like this:

SELECT { fn CURRENT_DATE() };

It’s a function, but not quite as we know it. They’re surrounded by { curly braces } and prefixed with fn. The kind of thing I’d expect to see in PowerShell.

It turns out they were introduced in ODBC versions 1-3, with the latter being based on the SQL-92 specification. These were introduced in 1995 and 1992 respectively, which likely explains why I don’t see them used.

They seem to act like regular functions, so you can use them as part of other functions too, for example:

SELECT Yesterday =
	DATEADD(DAY, -1,
	{ fn CURRENT_DATE() });

If you want to use these functions its worth checking return types. Some of them may cause unexpected results:

SET DATEFORMAT dmy;

SELECT Yesterday =
	DATEADD(DAY, -1,
	{ fn CURRENT_DATE() });

The CURRENT_DATE() function returns a VARCHAR, so the above returns 11th March ’24. That’s a little early to be having the Christmas decorations up 🎄

Some of them will return as expected, such as INT values from below:

SELECT CalWeek = { fn WEEK(GETDATE()) },
	CalQuarter = { fn QUARTER(GETDATE()) };

There are native alternatives for these so its unlikely we’d need them for modern applications. Some have identical names, such as HOUR(). Others are only available in specific versions, such as CURRENT_DATE (without parenthesis) in Azure SQL DB and Managed Instance. Otherwise, we typically have alternatives such as the examples below:

SELECT ODBC = { fn OCTET_LENGTH(N'Some String') },
	SQL = DATALENGTH(N'Some String');

SELECT ODBC = { fn TRUNCATE(12.34567, 3) },
	SQL = ROUND(12.34567, 3, 1);

SELECT ODBC = { fn CURRENT_DATE() },
	SQL = CAST(GETDATE() AS DATE);

SELECT ODBC = { fn DAYOFMONTH(GETDATE()) },
	SQL = DATEPART(DAY, GETDATE());

SELECT ODBC = { fn MONTHNAME(GETDATE()) },
	SQL = DATENAME(MONTH, GETDATE());

You can find a full list of these functions in the online documentation.

So, you may not have come across them before, and you’re even less likely to need them. But at least we know what they are now.

On the plus side, they’re supported on most flavours of SQL Server. So they’ve got that going for them.