IIF and CHOOSE Functions

The IFF and CHOOSE functions are non-standard T-SQL functions introduced in SQL Server 2012. These functions were added to help simplify migration from Microsoft Access.

IFF Function
Think of this as an If-Else function.

Example:

DECLARE
@a SMALLINT = 7,
@b SMALLINT = 19;
SELECT IIF(@a < @b, 'true', 'false'); -- Returns 'true'
GO

This is similar to CASE statement like below:
DECLARE
@a SMALLINT = 7,
@b SMALLINT = 19;
SELECT CASE WHEN @a < @b
THEN 'true'
ELSE 'false'
END -- Returns 'true'

Although IIF may seem like less code to write in most scenarios. It's still recommended to use standard functions unless you are migrating from Microsoft Access.
Link to Microsoft Documentation: https://msdn.microsoft.com/en-us/library/hh213574.aspx

CHOOSE Function
The CHOOSE function is straightforward. It will choose from a list of options based on the index position that you provided in the first parameter.

Example:

SELECT CHOOSE(2, 'eric', 'kyle', 'stan', 'kenny'); -- Returns 'kyle'
SELECT CHOOSE(4, 'eric', 'kyle', 'stan', 'kenny'); -- Returns 'kenny'
SELECT CHOOSE(0, 'eric', 'kyle', 'stan', 'kenny'); -- Returns NULL 

Link to Microsoft Documentation: https://msdn.microsoft.com/en-us/library/hh213019.aspx

Performing Date Searches in SQL Server 2012

Searching for data that contains both a date and time component can sometimes be tricky especially if you are looking for specific dates and times. I used to always have to open my sql book or google it when I am querying for something that involves dates. But with practice and a little bit of studying, here are some of the key points that I always try to remember whenever I am in this situation:

1. Always check if there is time component in the column. If your search does not require specific time, then proceed to #2 below.
2. Eliminate the time component in your query (I will show in the examples below).
3. If you do not specify a time component in your search parameter, for example: SELECT * FROM Sales.Orders WHERE orderdate < '2015-12-05', then a zero time component is added to it when converted to a datetime value. This conversion is implicit that is why it is very easy to miss or forget. Therefore, if the date columns you are searching have non-zero time components (for example, '2015-12-05 13:58:32.833), your query will not return that row(s).

How to ignore/remove time values:

I use some of the following techniques to do this:

- Use date type:

SELECT * FROM Sales.Orders WHERE CONVERT(date, orderdate) = '2015-12-05';

This technique is probably the easiest and the one I always use. However, it is important to also remember that this only works for SQL Server 2008 and later.

- Use range of dates:

SELECT * FROM Sales.Orders WHERE orderdate >= '2015-12-03' AND orderdate <= '2015-12-05';

This technique does not really involve any functions like CONVERT, but you need to be careful with this one. If your date has a non-zero component, it will not include it. For example, you have 2015-12-05 13:58:32.833, then the query will not include that record because orderdate is <= '2015-12-05' (which gets converted implicitly to '2015-12-05 00:00:00.000'). You can either do ...AND orderdate < '2015-12-06' if you want to include all the orders for 2015-12-05.

- Search using individual date components (i.e., MONTH, DAY, YEAR)

SELECT * FROM Sales.Orders WHERE MONTH(orderdate) = 12 

AND DAY(orderdate) = 05 AND YEAR(orderdate) 2015;

This gives you some flexibility and makes your query very specific. 

- Use CAST

SELECT * FROM Sales.Orders WHERE CAST(CAST(orderdate AS char(11)) as datetime) = '2015-12-05';

The inner CAST in the WHERE clause, first converts the datetime to a char type that's only 11 characters long. Doing this will truncate the time component. Then the outer CAST() converts the result of inner cast back to datetime. Again, since it is converted back to a datetime, there will be a zero time component added to it.

- Use CONVERT function

SELECT * FROM Sales.Orders WHERE CONVERT(datetime, CONVERT(char(10), orderdate, 110)) = '2015-12-05';

Almost similar to the CAST technique. The inner CONVERT function converts the datetime to a char type that's only 10 chars long, truncating the time component, and then the outer CONVERT function converts the result back to a datetime value with the zero time component. The '110' in the inner CONVERT indicates the date and time styles. You can find more details about this in the following Microsoft documentation: https://msdn.microsoft.com/en-us/library/ms187928.aspx#Anchor_3