How Does Date Conversion Work in SQL?

By Community / Developer
Oct 06, 2023

Navigate to:

Working with dates and times is crucial for tasks like timestamping entries, managing current events, keeping historical records, and tracking events. However, handling dates and times can be tricky for several reasons, like data being mismatched or not in the desired format or query outputs that don’t align with your needs. Luckily there are solutions. One approach is ensuring the data format is correct before it gets into your SQL database solution. Unfortunately, you can’t always control this. Still, another solution is to leverage SQL Server’s built-in date and time functions to streamline your date and time formatting complexities.

This post will explore date and time conversion in SQL Server by providing insights into the basics of SQL date conversion and various methods you can use. We’ll also offer short step-by-step tutorials, look at some case studies, and end with some best practices.

The Basics of SQL Date Conversion

Before diving into the various SQL built-in functionalities, it’s important that you understand the various date and time data types and formats of SQL. This foundational understanding will lay the groundwork for effective date manipulation and conversion with SQL.

In SQL Server, data is stored as a date or a date/time value in five different formats:

  • DATE, which comes in a YYYY-MM-DD format.
  • TIME, which comes in a hh:mm:ss format.
  • DATETIME, representing date and time, which comes in a YYYY-MM-DD HH:MI:SS format.
  • TIMESTAMP, which comes in a YYYY-MM-DD HH:MI:SS format.
  • YEAR, which is a four-digit format of YYYY.

Besides these date functions, SQL Server also has in-built date functions that let you get your dates. A few of these include the following:

  • GETDATE() gets you the current date and time.
SELECT GETDATE();

select get date

  • CURRENT_TIMESTAMP returns the current timestamp in the system.
SELECT CURRENT_TIMESTAMP;
  • SYSDATETIME() returns the server’s date and time.
Select SYSDATETIME();
  • SYSDATETIMEOffset() gets you the server’s date, time, and UTC offset.
Select SYSDATETIMEOffset();
  • DATEDIFF (date_part, start_date, end_date) is used to find the difference between two dates.
SELECT DATEDIFF(month, '2023-01-31 23:59:59', '2023-05-01 00:00:00');
  • DATEADD (date_part, number, date) is used to add numbers to a given date. For example, you can add 1 to the month value of your date using this syntax.
SELECT DATEADD(month, 1, '2023-08-10');

You should also check out this article on SQL date and time functions and how to get the current date for a more detailed and comprehensive understanding.

Different Methods for SQL Date Conversion

Now that you understand the various date data types, we can move on to the functions you can use for date conversion in SQL Server. Here are some of the various ways to convert data in SQL.

CAST

CAST is a built-in SQL conversion function that converts a value from one data type to another. You may use this function to convert a string to a date or extract a date from DATETIME.

The syntax for the CAST function is as follows:

CAST (expression AS [data type])

Let’s assume you want to change this string ‘12-12-2023’ to a date.

SELECT CAST('12-12-2023' AS date);

Similarly, you can also use CAST to extract the date from your current date and time.

SELECT CAST(getdate() AS date);

CONVERT

Another function is CONVERT, an in-built function that allows users to convert an expression from one data type to another. Thus, you can also use it to extract data from various data types.

The syntax for the CONVERT function is as follows:

CONVERT(data_type(length), expression, style)

Similarly to the CAST function, you can also use CONVERT to extract the date from your current date and time.

SELECT CONVERT(date, getdate());

You can extract the date from a DATETIME value.

SELECT CONVERT(VARCHAR(10), getdate(), 112);

The “112” used in this syntax above refers to the ISO standard date format. This 112 style will return in a yyyy/mm/dd format.

DATE_FORMAT

The DATE_FORMAT function formats a date as specified.

The syntax for the DATE_FORMAT function is as follows:

DATE_FORMAT(date, format)

Let’s see how this works.

SELECT DATE_FORMAT("2017-06-15", "%M %d %Y");

The %M, %d, and %Y denote the specifier determining how you will format your date value. In this instance, %M, %d, and %Y mean your date will appear with the month name (January…December), the day of the month in numeric (00…31), and the year in four digits.

The MySQL documentation highlights all the available specifiers.

FORMAT

The SQL FORMAT function formats your value with a format and an optional culture as specified. If you don’t specify any culture, SQL Server will use the language of your current session.

The syntax for the FORMAT function is as follows:

FORMAT(value, format, culture)

Here is an example:

DECLARE @d DATETIME ='2020-12-08 16:36:17.760';

SELECT FORMAT (@d, 'd', 'en-US') AS 'US English',
FORMAT (@d, 'D',  'de-de' ) AS 'German',
FORMAT(@d, 'f', 'kn-IN' )  AS 'Kannada',
FORMAT(@d, 'F', 'en-gb') AS 'Great Britain English';

Use Cases for Date Conversion Functions

Let’s now look at some practical use cases using the functions you just learned about.

How to Convert Date to Day in SQL?

You can use the FORMAT function to get the day from your date value for a situation like this.

SELECT GETDATE() 'Today Date', FORMAT(GETDATE(),'dddd') 'Date as Day'

select today date

How to Convert Date to Year in SQL?

Another situation would be trying to get the year from a date value. Let’s try the CONVERT function; you can easily do this with other functions we learned earlier.

SELECT GETDATE() 'Today Date', CONVERT(VARCHAR(4), getdate(), 120) 'Date as Year';

convert year

And if you’re looking for a powerful SQL client and database manager to build and scale your time-series-based applications quickly, then you should definitely try InfluxDB.

Common Mistakes to Avoid During SQL Date Conversion

When it comes to date conversion, there are some instances of date errors like “Conversion failed when converting date and/or time from character string” or the “Incorrect date format” error. If you’d like to avoid these errors, there are some common mistakes to avoid during SQL date conversion.

  • Incorrect date format: This error happens mainly because the date format differs between countries. So, a SQL database with a date and time of "08/15/2023 23:20:30" can be read as invalid for a format of "DD/MM/YYYY" as there is no "15" month in the Gregorian calendar. 
    --Wrong format
Declare @date_time_value varchar(100)= '08/15/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    Incorrect date format 
    --Correct format
Declare @date_time_value varchar(100)= '15/08/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    Declare date time value
  • Invalid or non-existent date: An error can be thrown because a non-existent date was attempted to be stored. 
    --Wrong format
Declare @date_time_value varchar(100)= '30/02/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    Invalid or non-existent date This error was thrown because there is no 30th day in February. 
    --Correct format
Declare @date_time_value varchar(100)= '28/02/2023 23:20:30'
select CONVERT(datetime2, @date_time_value, 103) as Date;
    Invalid or non-existent date
  • Misspelled and culture-specific date/time literals: This can happen if your SQL syntax contains a misspelled name or if your SQL Server is configured or designed to operate with specific date and time formats, such as English dates. In this instance, culture-specific date formats might not be compatible with SQL Server unless you modify the server settings. So, if your SQL Server is configured for English dates and times, it will only accept input in that format. A solution to this is to use the global standard ISO 8601 "YYYY-MM-DDThh: mm: ss" format, which is language-independent and recognized by almost all database systems. 
    SELECT CAST('2023 Septemeber 11' AS DATETIME);
    Misspelled and culture-specific date

Best Practices for SQL Date Conversion

To avoid the common mistakes mentioned earlier, you can follow some best practices to handle date conversion in SQL databases effectively. These practices are important because they not only guarantee compatibility across various systems but can also have a significant influence on your data accuracy and query performance.

  • Avoid writing dates in a culture-specific or regional format, as it can mean different dates depending on who is reviewing. Sticking with the global standard ISO 8601 format will be a much better approach.

  • Use comparison operators like >= and <= over BETWEEN. While BETWEEN is great for integers, it can create some date ambiguity, especially for close-range data. Comparison operators have an inclusive nature and give you more control as you can specify the exact range you’re interested in down to the level of precision you need, even when working with databases that store timestamps with milliseconds or more precise time components. Additionally, they also promote consistency across various SQL systems.

  • Always store your date value in DATE, DATETIME, DATETIMEOFFSET, or preferably DATETIME2 instead of a text data type (VARCHAR, CHAR, NVARCHAR, TEXT, or CHAR) as they provide more precision.

Conclusion

To wrap up, let’s recap everything we covered in this post.

First, we covered the essentials of SQL date conversion. We started by emphasizing the importance of understanding date data types. Then we introduced you to different ways to convert dates in SQL before diving into some practical examples. From there, we looked at some common mistakes to avoid and highlighted some best practices to adhere to during date conversion.

Your journey with SQL and its in-built functions doesn’t end here, however—SQL is vast. You can continue learning from the InfluxData blog and explore the InfluxDB Cloud interface, which supports native SQL queries. The new InfluxDB cloud storage engine also offers Flight SQL support, enabling you to leverage third-party SQL tools for your projects.

Additional resources

This post was written by Ifeanyi Benedict Iheagwara. Ifeanyi is a data analyst and Power Platform developer who is passionate about technical writing, contributing to open source organizations, and building communities. Ifeanyi writes about machine learning, data science, and DevOps, and enjoys contributing to open-source projects and the global ecosystem in any capacity.