I’m currently preparing to take the exam 70-761 – Querying Data with Transact-SQL and have been going through various resources including Itzit Ben-Gan’s exam guidebook which is certainly worth reading if you’re planning on taking this exam.
There are quite a few smaller bits of T-SQL functionality that I’m not that familiar with that I’m going to demonstrate in this and further posts. It’s a bit of a random selection but hopefully it will help me remember them and might be of use to others as well.
- Date and Time Functions
- IIF (Inline IF Function)
- CHOOSE Function
- String Functions
1. Date and Time Functions
There are quite a few different Date and Time functions available in SQL Server. There are a few I used regularly such as GETUTCDATE, DATEPART and DATEADD but some I hardly use or have never used at all. I can never remember the syntax for most of them so I’m going to give an example of each one along with a bit of detail about each.
Returning current date and time
The following functions all return the current date and time.
The timezone of my PC is BST.
This function is specific to SQL Server and returns a DATETIME value containing the current date and time.
This function is specific to SQL Server and returns a DATETIME value containing the current UTC date and time.
This function is SQL standard and returns a DATETIME value containing the current date and time.
This function is SQL standard and returns a DATETIME2(7) value containing the current date and time.
This function is SQL standard and returns a DATETIME2(7) value containing the current UTC date and time.
This function is SQL standard and returns a DATETIMEOFFSET value containing the current date, time and offset.
As mentioned above this is BST which is 1 hour ahead of UTC.
2018-04-30 22:13:18.4048719 +01:00
These following two functions can be used to extract different parts of a supplied date and time.
For example, the following will return the month number from the supplied date or time parameter (please note I’m passing in a VARCHAR value but this is implicitly converted to a DATETIME2 data type).
SELECT DATEPART(MONTH, '20180430');
The following will return the minute value from the supplied date time value.
SELECT DATEPART(MINUTE, '20180430 03:23:17');
If no time part is included in the date value then it’s assumed to be midnight which is why this returns 0.
SELECT DATEPART(MINUTE, '20180430');
The following returns the number of days since the 1st Jan.
SELECT DATEPART(DAYOFYEAR, '20180430');
The following returns any offset in minutes. The offset is three hours so this returns 180 minutes.
SELECT DATEPART(TZoffset, '20180430 13:30:00 +03:00')
DATEPART always returns an INT value.
We can use the DATENAME function to return textual values for date parts.
For example, the following returns the month name for the supplied date.
SELECT DATENAME(MONTH, '20180430')
The DATENAME function is language dependent and will return the value in the language of the user running the query.
We can use the following functions to return date and time values from integer date parts. Each function requires a different number parameters and all are mandatory in order to build up the returned value correctly.
The following returns a DATE value.
The following returns a DATETIME value.
All 7 parameters in this one are required but we can obviously use zeroes if we don’t have the required level of precision.
The following returns a DATETIME2 type with an eighth parameter used to specify the precision of the data type.
The “fraction” parameter (the seventh one) is related to the precision which determines how many digits are allowed in the “fraction” parameter.
If we’re only allowing a precision of 1 we can’t specify 2 digits in the “fraction” parameter.
Msg 289, Level 16, State 5, Line 89
Cannot construct data type datetime2, some of the arguments have values which are not valid.
If we are using a precision of 0, which is just to the nearest second, the “fraction” parameter must be 0 otherwise we get the same error as above.
Msg 289, Level 16, State 5, Line 53
Cannot construct data type datetime2, some of the arguments have values which are not valid.
The following returns a DATETIMEOFFSET value with the offset hour and minute parameters before the precision.
2018-04-30 19:42:34.0 +02:30
The following function returns a DATE value containing the last date in the month of the supplied date.
There is an optional offset parameter that can move forward or back the specified number of months
SELECT EOMONTH('20180315', -1);
SELECT EOMONTH('20180315', 2);
There isn’t a “start of month” function but further down I show how to do this using the DATEADD and DATEDIFF functions.
The DATEADD function can be used to add a specified number to the specified date part of the supplied date time value and returns a DATETIME value.
For example, the following adds 10 days to 10th April 2018.
SELECT DATEADD(DAY, 10, '20180410');
The following one takes 1 month from 10th April 2018.
SELECT DATEADD(MONTH, -1, '20180410');
This function compares a certain date part value between two datetime values and returns an INT value.
For example, the following calculates that there are 2 days difference between 1st April and 3rd April 2018.
SELECT DATEDIFF(DAY, '20180401', '20180403');
If the number returned is very large we can use this function to return the value as a BIGINT.
SELECT DATEDIFF_BIG(MILLISECOND, '19000101', GETUTCDATE());
Above we saw we can use the built in EOMONTH function to get the last day of the month for the supplied date.
Here’s a nice little trick to get the first date of the supplied month, in this case the current month. I’m running this on 30th April 2018
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETUTCDATE()), 0);
To explain how this works let’s first look at the DATEDIFF part that is passed in as the second parameter in the DATEADD function.
The start date parameter is supplied as zero. This defaults to 1st Jan 1900.
So the following two statements return the same value which is the number of months since 1st Jan 1900.
I’m running this in April 2018 so this returns 1419
SELECT DATEDIFF(MONTH, 0, GETUTCDATE())
SELECT DATEDIFF(MONTH, '19000101', GETUTCDATE())
The DATEADD function also uses 0 as the date parameter which again defaults to 1st Jan 1900.
So this function is saying “add 1419 months to 1st Jan 1900” and this gives us 1st April 2018.
We can use the TODATETIMEOFFSET function to add an offset to a date time value. This returns a DATETIMEOFFSET value.
SELECT TODATETIMEOFFSET('20180430 10:00', '+02:00')
2018-04-30 10:00:00.0000000 +02:00
We can use the SWITCHOFFSET function to switch from one time zone to another.
The first parameter has to be a DATETIMEOFFSET value and the second is the offset from UTC. I’ve run this at 21:16 BST which is UTC + 1.
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+02:00');
2018-04-30 23:13:18.4048719 +02:00
We can also add an offset using “AT TIME ZONE” and specifically specify the time zone as follows.
SELECT CAST('20180415 17:32' AS DATETIME) AT TIME ZONE 'Central European Standard Time'
2018-04-15 17:32:00.000 +02:00
2. IIF (Inline IF function)
Most programming languages provide some sort of ternary or condition operator and SQL Server is no different. It provides the Inline IF function (IIF)
This function accepts three parameters. The first is a boolean, or an expression that returns a boolean. If the boolean value is true then the value supplied in the second parameter is the value returned by the function. If the boolean value is false then the value supplied in the third parameter is the value returned by the function.
SELECT IIF(1=1, 1, 0)
SELECT IIF(1=0, 1, 0)
If you are more familiar with the CASE statement, the queries above are equivalent to the following…
SELECT CASE WHEN 1=1 THEN 1 ELSE 0 END
SELECT CASE WHEN 1=0 THEN 1 ELSE 0 END
3. CHOOSE Function
The CHOOSE function accepts at least 2 parameters.
The first parameter is the “index” and has to be an INT that says which value from the preceding parameters should be returned.
The following examples all use four parameters meaning there are three values available to chose from the parameters supplied after the “index” parameter.
The following will return the value in the first parameter after the “index” value
The following will return the value in the second parameter after the “index” value
The following will return the value in the third parameter after the “index” value
If we pass an “index” value greater than the number of values to choose from then the function returns NULL.
4. String Functions
There are several string functions provided in SQL Server and I’m going to demonstrate some of the ones I find most useful…
The following repeats the string “ab” 5 times…
SELECT REPLICATE('ab', 5);
The following reverses the supplied string…
The SPLIT_STRING table valued function will split a string based on a supplied separator. This is available from SQL Server 2016 onwards.
The following splits the string into a data set of rows using the underscore character as the separator…
FROM STRING_SPLIT('My_Name_Is_Simon', '_')
The STUFF function is one of the most powerful string manipulation tools.
It can be used to insert (or stuff) a string at any point inside another string.
It can be used to replace a certain number of characters at any point within a string and replace them with another string.
The scalar function returns a VARCHAR and accepts four parameters…
1. This is the original string supplied for modification
2. This is the starting point for the string manipulation
3. This is the number of characters in the original string that are going to be deleted starting at the character supplied in parameter 2
4. This is the string that we want to “stuff” into the original string at the character supplied in parameter 2
In the following example the original string is “abcde”. Parameter 2 says we’re going to perform the “stuff” at character 1, i.e. at character “a”. Parmeter 3 says we’re deleting 0 characters from the original string and the final parameter is the the string we want to “stuff” in at character 1.
This will insert the character “x” at the beginning of the original string…
We can’t use STUFF to add “x” to the end of the string. We would want to place it as the 6th character in this example but if we try this is just returns NULL.
However, add a string to the end of another is just simple concatenation.
SELECT 'abcde' + 'x'
SELECT CONCAT('abcde', 'x')
Now let’s try and replace the “c” character with the “x” character using the STUFF function…
SELECT STUFF('abcde', 3, 1, 'x')
Obviously this is not specifically looking for the “c” character as the REPLACE function would, it’s just replacing the third character in the original string with the string in the fourth parameter.
If we want to replace the second and third characters of the string we can do the following…
SELECT STUFF('abcde', 2, 2, 'x')
This is saying delete the two characters starting at character 2, i.e. “bc”, and insert the string in the fourth parameter, i.e. “x”.
The fourth parameter doesn’t have to be a single character…
SELECT STUFF('abcde', 2, 2, 'alongerstring')
The statement won’t error if the third parameter is a bigger number than the number of characters in the original string.
For example, if we know the original string is less than 100 characters long we can run the following to replace everything but the first character…
SELECT STUFF('abcde', 2, 100, 'XXXXXXX')
SELECT STUFF('fghijklmnopq', 2, 100, 'XXXXXXX')
SQL Server now supplies dynamic data masking functions but we could use STUFF to do something similar such as mask all but the last four digits of a credit card.
Card numbers are typically 16 digits long so we could delete the first 12 digits by starting at digit 1 supplying 12 as the third parameter. We could then replace that deleted part of the string with 12 asterisk characters…
SELECT STUFF('1234567812345678',1 , 12, REPLICATE('*', 12));
One final thing on STUFF is that it can be used along with FOR XML to concatenate rows of string data into a single string. Details of this can be found here.
The following functions extract a certain number of characters from a supplied string
This returns the first 2 characters on the left…
SELECT LEFT('abcde', 2);
This returns the last 2 characters…
SELECT RIGHT('abcde', 2);
This returns the 3 character starting from character 2…
SELECT SUBSTRING('abcde', 2, 3);
I’m only going to show one example of the FORMAT function as there is quite a lot to this one. For full information see the MS documentation.
This example will format a supplied UK phone number into a more readable format…
SELECT FORMAT(07777123678, '+44 (0) #### ### ###');
Please note as the value supplied in the first parameter is a number the leading zero is ignored.
There are several other string functions available and please see the MS documentation for full details.