SQL Server date format is an important aspect of working with date and time data in a database management system. Two common date formats used in SQL Server are dd-mm-yyyy and mm-dd-yyyy.

The dd-mm-yyyy format is also known as the "day-month-year" format. In this format, the day is listed first, followed by the month and then the year. For example, the date January 1, 2023, would be written as 01-01-2023. This format is commonly used in countries like the UK, Australia, and India.

On the other hand, the mm-dd-yyyy format is also known as the "month-day-year" format. In this format, the month is listed first, followed by the day and then the year. For example, the date January 1, 2023, would be written as 01-01-2023. This format is commonly used in countries like the United States and Canada.

It is important to note that the date format used in SQL Server can have an impact on how dates are sorted and displayed in query results. If you are working with dates in SQL Server, it is essential to understand the date format being used and to ensure that it is consistent throughout your application.

SET DATEFORMAT

To set the date format in SQL Server, you can use the SET DATEFORMAT command. For example, to set the date format to dd-mm-yyyy, you can use the following SQL statement:

SET DATEFORMAT dmy;

Similarly, to set the date format to mm-dd-yyyy, you can use the following SQL statement:

SET DATEFORMAT mdy;

Example

Examples of working with dates in SQL Server:

Date format dd-mm-yyyy

DECLARE @dateString VARCHAR(10) = '30-03-2023'
DECLARE @date DATE = CONVERT(DATE, @dateString, 105)
SELECT @date -- Output: 2023-03-30

In the CONVERT function, the third parameter 105 indicates the date format of the input string.

Date format mm-dd-yyyy

DECLARE @dateString VARCHAR(10) = '03-30-2023'
DECLARE @date DATE = CONVERT(DATE, @dateString, 101)
SELECT @date -- Output: 2023-03-30

In this case, the date format code is 101.

Change date format

DECLARE @date DATE = '2023-03-30'
SELECT CONVERT(VARCHAR(10), @date, 101) -- Output: 03/30/2023

The second parameter of the CONVERT function is the target format code, which in this case is 101 again.

Using a date variable in a query

DECLARE @startDate DATE = '2023-01-01'
DECLARE @endDate DATE = '2023-03-31'
SELECT * FROM sales
WHERE saleDate BETWEEN @startDate AND @endDate

Here we declare two date variables and use them in a query to filter the sales table between the start and end dates.

Conclusion

Handling dates in SQL Server can be challenging due to the variety of formats, regional settings, and specific application requirements. However, by understanding the behavior of SQL Server and following best practices, developers can effectively manage dates and ensure consistent results.

Using the SET DATEFORMAT command to enforce dd-mm-yyyy or employing explicit conversion functions like FORMAT are critical tools in a developer's toolkit. Adopting these practices ensures data integrity, accurate reporting, and compliance with business or legal standards.

Feel free to experiment with the techniques outlined in this blog and tailor them to meet your specific project requirements. Correctly managing dates today will save significant time, effort, and potential issues in the future!