Data Types and Functions Archives

Knowing which data types and functions to use ads flexibility to your query repertoire. Each column in a table is defined by a data type, which in turn can server as an input to a function.

In this section is an archive of article covering the best data types and functions posts we have made.

If you’re looking for a good place to start, I would start with

Seven SQL Server Data Types you Must Know to get an understanding of to main types.

One you’ve done that, read

Built-In Functions (SQL Server) to get a good introduction to the various functions you can use to perform calculations, such as setting text to upper case, or determining the month from a date.

All the examples for this lesson are based on Microsoft SQL Server Management Studio and the sample databases AdventureWorks and WideWorldImporters. You can get started using these free tools using my guide Getting Started Using SQL Server

SQL COALESCE Function and NULL

Knowing when to use the SQL COALESCE function is a lifesaver when you’re dealing with NULL. As you know, NULL is a tricky concept, and it seem what ever NULL “touches” in an expression, it renders the result NULL. So, when you’re dealing with NULL, how can you break out of the cycle? That is, […]

Can I have a CASE Statement in the WHERE Clause?

A very common question I get asked is whether I can have a CASE Statement in the WHERE Clause. There are a number of examples of CASE being used in SELECT columns, or in ORDER BY that we tend to forget CASE can be used wherever an expression is expected. Where Can I use a […]

How do I handle a “Error converting data type” error?

A reader recently asked about a error converting data type error they received. Since this is a common issue, especially when numeric data is stored withing VARCHAR datatypes, I thought you would appreciate the answer I shared with them. I’m using Windows 10 and SQL SMS 2014. When I run a query, I get the […]

How to use the Choose Function with Select

In this video we’ll walk you though how to use the CHOOSE function with SELECT. You’ll see how the function works and then a practical example using some sample data. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have […]

Common Data Types used in SQL Server

In this video we’ll walk you though the common data types used in SQL Server; you’ll see examples of each type’s values, and how to define them. Once you’ve gone through this article, I would recommend watching our next Essential SQL Minute to continue learn more about SQL Server! Once you have watched the video […]

Uncommon SQL Server Data Types

In this article we’re going to go over a uncommon SQL server data types. These are those you won’t use every day, but you’ll want to know if you take the 70-461 exam. Even if you don’t plan on taking the 70-461, learning these data types is fun, especially the spatial datatype (I had no idea […]

Calculate the Last Day of the Month using SQL

When working with SQL dates, sometimes you need to calculate the end of the month. Months are tricky! Some are 28 days, others 30 or 31, and now and then there’s a leap year! So, given a date, how do you calculate the number of days remaining in the month? The calculation is really a […]

Know When to Use Cast versus Convert

In this article, we will be exploring the CAST and CONVERT functions to understand whether there are any significant differences worth knowing when we want to data from one type to another. Since the CAST and CONVERT can be used interchangeably in most situations, you may wonder if one function is better than the other. […]

Learn to use SQL Server Date Functions

SQL server’s date functions provide you a set of function that you can use to manipulate dates. The function are used for a wide variety of operation such as adding weeks to a date, calculating the difference between two dates, or to decompose a date into its fundamental parts. If you not familiar with SQL […]

Introduction to SQL Server’s Common String Functions

The built in SQL String functions make it possible for you to find and alter text values, such as VARCHAR and CHAR datatypes, in SQLServer. Using these functions you can alter a text value such as changing “Smith, Joe” to “Joe Smith.” If you not familiar with SQL functions, then I would recommend staring with […]

Introduction to SQL Server’s Mathematical Functions

Mathematical Functions to Old Way - Slide Rule!

Math Rocks! SQLServer includes many mathematical functions you can use to perform business and engineering calculations. Many of these aren’t used in typical day-to-day operations; however, there are several commonly used functions we’ll cover. If you not familiar with SQL functions, then I would recommend staring with the Introduction to SQL Server Built-In Functions. All […]

Introduction to SQL Server’s Built-In Logical Functions

Logical functions provide a way to use logical conditions to display one of several values. You can use logical functions to test a field’s value such as gender (M or F) and display another value(‘Male’ or ‘Female’) as a result. In this article we describe how to use the CHOOSE and IIF functions. CHOOSE is […]

Built-In Functions (SQL Server)

Built-In functions are used in SQL SELECT expressions to calculate values and manipulate data. These functions can be used anywhere expressions are allowed. Common uses of functions include to change a name to all upper case. In this article we’ll introduce you to basic concepts. All the examples for this lesson are based on Microsoft […]

Converting Between Data Types with SQL Server Functions

SQL server uses data types to store a specific kind of value such as numbers, dates, or text in table columns and to use in functions, such as mathematical expressions. One issue with data types is that they don’t usually mix well. Conversion functions make them mix better! Though there are time when types are […]

SQL CASE Statement – Simple and Searched Forms

The main purpose of a SQL CASE expression returns a value based on one or more conditional tests. Use CASE expressions anywhere in a SQL statement an expression is allowed. Though a truly an expression, some people refer to them as “CASE statements.” This most likely stems from their use in programming languages. The SQL […]

What is a Database NULL Value?

What is a Null Value? In databases a common issue is what value or placeholder do you use to represent a missing values. In SQL, this is solved with null. It is used to signify missing or unknown values. The keyword NULL is used to indicate these values. NULL really isn’t a specific value as much as it […]