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.

In conclusion, understanding the SQL Server date format is important for working with date and time data in a database management system. The dd-mm-yyyy and mm-dd-yyyy formats are two common date formats used in SQL Server and can have an impact on how dates are sorted and displayed in query results.