How to Format Datetime in SQL Server for Dev

Dear Dev, if you’re working with SQL Server and handling datetime values, you might have found yourself in need of formatting them in a certain way. Fortunately, SQL Server provides a variety of functions and options to help you achieve this. In this article, we’ll go over the different ways you can format datetime values in SQL Server, along with some examples and practical tips.

Understanding Datetime in SQL Server

Before we dive into the formatting options, let’s review some basics about datetime in SQL Server. In SQL Server, datetime data types are used to represent dates and times, with varying degrees of precision. The main datetime data types are:

Datetime Type	Description
DATE	Represents a date without time
TIME	Represents a time without date
DATETIME	Represents both date and time, with a precision of 3.33 milliseconds
SMALLDATETIME	Represents both date and time, with a precision of 1 minute

Keep in mind that datetime values are stored internally as a number of ticks, which means that they can be manipulated mathematically if needed. However, when displaying them to users or integrating them with other systems, you’ll often need to format them in a human-readable way. Let’s see how you can do that.

Formatting Datetime Values with CONVERT

The CONVERT function is one of the most commonly used functions for datetime formatting in SQL Server. It allows you to convert a datetime value from one data type to another, while also applying a specific format. The general syntax of CONVERT is:

CONVERT(target_data_type, expression, style)

Where:

• target_data_type is the data type you want to convert the expression to
• expression is the datetime or datetime2 expression you want to convert
• style is an integer value that represents the format you want to apply

Here are some examples:

Converting Datetime to String with Default Format

If you simply want to convert a datetime value to a string using the default format for the target data type, you can use CONVERT with style 0 (zero). For example:

SELECT CONVERT(VARCHAR(10), GETDATE(), 0)

This will return a string in the format of ‘mm/dd/yy’ for DATE or ‘mm/dd/yy hh:mi:ss’ for DATETIME or SMALLDATETIME.

Converting Datetime to String with Custom Format

If you want to apply a custom format to the datetime value, you can use one of the predefined styles that correspond to the desired format. Here are some examples:

Style	Description	Example
101	mm/dd/yyyy	SELECT CONVERT(VARCHAR(10), GETDATE(), 101)
102	yyyy.mm.dd	SELECT CONVERT(VARCHAR(10), GETDATE(), 102)
103	dd/mm/yyyy	SELECT CONVERT(VARCHAR(10), GETDATE(), 103)
104	dd.mm.yyyy	SELECT CONVERT(VARCHAR(10), GETDATE(), 104)
105	dd-mm-yyyy	SELECT CONVERT(VARCHAR(10), GETDATE(), 105)

Note that the output of CONVERT will be a string, so if you need to use the converted value in a mathematical operation, you’ll need to convert it back to a datetime data type.

Formatting Datetime Values with FORMAT

In addition to CONVERT, SQL Server 2012 introduced a new function called FORMAT that allows you to format datetime values in a more flexible and readable way. FORMAT is similar to the .NET Framework’s formatting functions, and supports a wide range of formatting options.

The general syntax of FORMAT is:

FORMAT(expression, format)

Where:

• expression is the datetime or datetime2 expression you want to format
• format is a string that specifies the desired format

Here are some examples:

Formatting Date with Custom Format

If you simply need to format a DATE value, you can use FORMAT with the desired custom format. For example:

SELECT FORMAT(CAST('2022-12-31' AS DATE), 'ddd, MMMM d, yyyy')

This will return a string in the format of ‘Saturday, December 31, 2022’.

Formatting Datetime with Custom Format

If you need to format a DATETIME or SMALLDATETIME value, you can combine FORMAT with CONVERT to first convert the value to a string, and then apply the desired format. For example:

SELECT FORMAT(CONVERT(DATETIME, '2022-12-31 23:59:59'), 'F')

This will return a string in the format of ‘Sunday, December 31, 2022 11:59:59 PM’.

Common Datetime Formatting Patterns

Knowing the available formatting options is useful, but it can also be overwhelming. Here are some common formatting patterns that you might find useful:

ISO 8601 Date Format

The ISO 8601 date format is widely used and recognized, and has the advantage of being unambiguous and machine-readable. The format is:

yyyy-MM-dd

To format a DATE value as ISO 8601, you can use CONVERT with style 120:

SELECT CONVERT(VARCHAR(10), GETDATE(), 120)

This will return a string in the format of ‘yyyy-mm-dd’.

To format a DATETIME value as ISO 8601, you can use FORMAT with the custom format ‘yyyy-MM-ddTHH:mm:ss’:

SELECT FORMAT(GETDATE(), 'yyyy-MM-ddTHH:mm:ss')

This will return a string in the format of ‘yyyy-mm-ddTHH:mm:ss’.

Month/Day/Year Format

If you need to display the date in a more traditional US format, you can use style 101 with CONVERT:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101)

This will return a string in the format of ‘mm/dd/yyyy’.

Frequently Asked Questions

Can I use datetime functions in WHERE clauses?

Yes, you can use datetime functions in WHERE clauses to filter records based on their date or time values. For example:

SELECT * FROM Orders WHERE DATEADD(day, 7, OrderDate) < GETDATE()

This will retrieve all the orders whose “OrderDate” is more than 7 days ago.

How do I calculate the difference between two dates?

You can use the DATEDIFF function to calculate the difference between two dates. The syntax of DATEDIFF is:

DATEDIFF(interval, start_date, end_date)

Where:

• interval is the unit of time you want to calculate the difference in (e.g. day, hour, minute)
• start_date is the earlier date
• end_date is the later date

For example, to calculate the number of days between two dates:

SELECT DATEDIFF(day, '2022-01-01', '2022-01-31')

This will return the value 30.

Can I use datetime values in calculations?

Yes, you can use datetime values in calculations by treating them as numbers of ticks. For example, to add 2 days to a DATETIME value:

SELECT DATEADD(day, 2, OrderDate) FROM Orders

This will return the “OrderDate” value of each order, plus 2 days.

What if I need a custom datetime format that isn’t covered by CONVERT or FORMAT?

If you need a custom datetime format that isn’t covered by the built-in functions, you can use string manipulation functions like LEFT, RIGHT, and SUBSTRING to extract the desired parts of the datetime value, and then concatenate them in the desired order. For example:

SELECT CONCAT(RIGHT(CONVERT(VARCHAR(23), GETDATE(), 121), 12),' ',LEFT(CONVERT(VARCHAR(23), GETDATE(), 121), 2))

This will return a string in the format of ‘hh:mm:ss AM/PM’.

Conclusion

We hope this article has given you a good overview of the different ways you can format datetime values in SQL Server. Whether you prefer the simplicity of CONVERT or the flexibility of FORMAT, you now have the tools to output datetime values in the format that best suits your needs. If you have any questions or feedback, feel free to leave a comment below. Happy formatting!