SQL Cast as Date: Definition and Applications

By Community / Developer
Nov 13, 2023

Navigate to:

This post is a guide to the SQL “cast as date” function. What does the function do, and why should you care about using it?

Time and date manipulation is an everyday task when working with databases. Funny enough — or maybe not so funny — you won’t always find dates represented with the proper date formats. In such cases, you’ll have to cast or convert to date formats, and that’s what this post is all about.

We’ll open by explaining what the prerequisites are if you want to follow along with this guide. Then, we’ll follow with a broad overview of the CAST function in SQL. You’ll understand what the term SQL “cast as date” means, how the function works, and the details of its syntax.

After that, you’ll learn about common use cases for the function. Finally, we’ll show you more details of how to use the function.

Prerequisites

To follow along with the post, there are two prerequisites you must comply with:

Without further ado, let’s get started.

What Does “Cast as Date” Mean in SQL?

What does “cast as date” even mean in SQL? Well, casting is an operation by which you take a value in a given data type and transform it into a different type. (This is true not only in the context of relational databases but in programming in general.) Unsurprisingly, the value that you wish to cast needs to be compatible with the destination for type. For instance, if you want to cast a piece of text to a date, the text must adhere to a valid data format.

In SQL, CAST is a function. You can cast values to different types, and the types related to date and time aren’t an exception. The syntax for the CAST function is super simple:

CAST ( expression AS data_type [ ( length ) ] )

A quick explanation:

  • The expression is the value you want to cast
  • data_type refers to the type you want to cast the expression to
  • length is optional and refers to the length of the destination type

Now you’re familiar with the terms, let’s proceed to why they’re important. What are the motivations behind using the function? That’s what we’ll see next.

SQL “Cast as Date” Main Use Cases

There are several interesting use cases for casting values as dates. With that in mind, here I use “cast as date” very loosely to mean any operation in which a value is converted to date or another related type. As you’ll soon see, there’s an alternative for the CAST function, and some of the following use cases use that alternative instead:

  • Date formatting: It’s possible to cast or convert values in order to format dates to the desired format for display or even specify a given format when parsing dates in text format.

  • Date comparison: It’s possible to use the CAST function in order to convert values to date and then perform a comparison.

  • Extracting components of dates: You’ll often have a value with the date and time, but you want only either of those. It’s possible to cast the value to a new one containing only the desired component.

Working With SQL “Cast as Date”

With the basics out of our way, let’s now walk you through how to use the SQL CAST function in practice. We’ll do this in a Q&A format by presenting common usage scenarios as questions and then answering them.

How Do I Cast a String to a Date?

Probably the most common usage scenario for the SQL “cast as date” operation is wanting to cast a string to a date format. Let’s start by covering how to cast a simple string containing a date in the ISO 8601 format to the date type:

SELECT CAST('2023-09-25' AS date) AS MyDate

This is the result:

MyDate
2023-09-25

It may not seem like much. After all, it’s the exact same value. But you have to understand that the value returned by the function is of the date type, which means you can use it, for instance, in date calculation. Let’s say that you want to add 10 days to the resulting date. Here’s how you’d go about it:

SELECT DATEADD(Day, 10, CAST('2023-09-25' AS date)) AS MyDate

How to Cast Today’s Date in SQL

In SQL Server, if you want to retrieve the current date, you must use the aptly named GETDATE() function:

SELECT GETDATE() AS Now

If you go ahead and run the query above, you’ll see that it returns not only the current date but also the time. This happens because the function’s return value is of the datetime type, which contains both components. What if the date is all you want? If that’s the case, you can easily cast the resulting value to a date:

SELECT CAST(GETDATE() AS date) AS Today

Now, let’s say you wanted only the time component. In that case, you’d cast the resulting value to time:

SELECT CAST(GETDATE() AS time) AS Now

How to Cast String to Date in a Different Format?

Of course, we humans can’t agree on the right date format to use, resulting in a myriad of competing formats. You’ll often need to cast a string to a date, time, or datetime value, but the date within the string doesn’t adhere to the ISO 8601 format. How do you go about solving this problem?

Luckily for you, SQL Server has the solution in the form of the CONVERT function. Let’s see an example:

SELECT CONVERT(datetime, '25/09/2023', 103) AS MyDate

The function doesn’t look complicated to understand—the only enigmatic part is that 103 at the end. Let’s cover its arguments, one at a time:

  • datetime: This is the destination type for the conversion.

  • ‘25/09/2023’: This is the date we wish to convert in the dd/mm/yyyy format.

  • 103: This is the style used to determine the format for conversion.

To see a list of all available styles, you can refer to Microsoft’s documentation.

cast-function

What is the Difference Between “Cast as Date” and “Convert Date” in SQL?

You might wonder: What’s the difference between the SQL “cast as date” and CONVERT functions? After all, don’t they do the same thing?

There are differences in functionality, as you’ve just seen. But the most striking difference between the versions is that the CAST function belongs to the ANSI SQL specification, while CONVERT is specific to SQL Server.

Summing It Up

As you’ve seen, SQL “cast as date” simply refers to the usage of the CAST function in SQL in order to cast values to date types optimized to handle dates. This operation has many useful applications: By casting values to date, time, or datetime types, you’re able to perform useful operations on them, such as time arithmetic.

Casting can also be used for formatting purposes and even to extract a specific component from a value, such as obtaining just the time from a datetime value.

As you’ve also seen, the CAST function has an easy syntax, and it’s quite intuitive to use. It’s also somewhat limited—when you find yourself in slightly more advanced scenarios, you might want to use the CONVERT function instead.

Additional resources: