Date Time Format SQL Server

Hi Dev! If you are working with SQL Server, then you must have come across date and time formats. Date and time formats are essential in storing, converting, and displaying dates and times. This article will provide you with an in-depth understanding of date and time formats in SQL Server, including their syntax, usage, and examples.

What is Date Time Format?

Date and time format is a standard way of representing dates and times in a format that is readable by both humans and computers. In SQL Server, date and time formats are used to store, convert, and display dates and times.

The syntax for date and time formats in SQL Server includes date parts, time parts, and separators. Date parts include the year (yyyy), month (mm), and day (dd). Time parts include the hour (hh), minute (mi), second (ss), and millisecond (ms). Separators are used to separate date and time parts, and they can be any non-alphanumeric character.

For example, the default date and time format in SQL Server is yyyy-mm-dd hh:mi:ss.mmm. This format represents the year (yyyy), month (mm), day (dd), hour (hh), minute (mi), second (ss), and millisecond (mmm) in a format separated by hyphens (-) and colons (:).

Table 1: Default Date Time Format in SQL Server

Date Time Format	Example
yyyy-mm-dd hh:mi:ss.mmm	2021-10-15 13:30:45.123

Common Date Time Formats in SQL Server

SQL Server supports various date and time formats, and each has a specific syntax and usage. The following are common date and time formats in SQL Server:

1. Date Only Format (yyyy-mm-dd)

The date only format represents the year (yyyy), month (mm), and day (dd) only, without any time parts. This format is useful when you need to work with dates only, without any time information.

The syntax for the date only format in SQL Server is yyyy-mm-dd. For example:

Table 2: Date Only Format in SQL Server

Date Only Format	Example
yyyy-mm-dd	2021-10-15

2. Time Only Format (hh:mi:ss.mmm)

The time only format represents the hour (hh), minute (mi), second (ss), and millisecond (mmm) only, without any date parts. This format is useful when you need to work with times only, without any date information.

The syntax for the time only format in SQL Server is hh:mi:ss.mmm. For example:

Table 3: Time Only Format in SQL Server

Time Only Format	Example
hh:mi:ss.mmm	13:30:45.123

3. Short Date Format (dd/mm/yyyy)

The short date format represents the day (dd), month (mm), and year (yyyy) only, separated by slashes (/). This format is commonly used in many countries outside the US.

The syntax for the short date format in SQL Server is dd/mm/yyyy. For example:

Table 4: Short Date Format in SQL Server

Short Date Format	Example
dd/mm/yyyy	15/10/2021

4. Long Date Format (dd month yyyy)

The long date format represents the day (dd), month (written out in words), and year (yyyy) only. This format is commonly used in the UK.

The syntax for the long date format in SQL Server is dd month yyyy. For example:

Table 5: Long Date Format in SQL Server

Long Date Format	Example
dd month yyyy	15 October 2021

Using Date Time Formats in SQL Server

Once you know the syntax and usage of date and time formats in SQL Server, you can use them in various SQL queries and operations.

1. Storing Dates and Times

In SQL Server, you can store dates and times in different data types, such as datetime, date, time, and datetime2.

The datetime data type is used to store both date and time information, with a precision of up to 3.33 milliseconds. The date data type is used to store date only information, without any time information. The time data type is used to store time only information, without any date information. The datetime2 data type is similar to datetime, but with a higher precision of up to 100 nanoseconds.

To store dates and times in SQL Server, you can use the INSERT statement, followed by the date and time values in the specified format. For example:

Table 6: Storing Dates and Times in SQL Server

SQL Query	Output
INSERT INTO MyTable (MyDate) VALUES (‘2021-10-15’)	2021-10-15 00:00:00.000
INSERT INTO MyTable (MyTime) VALUES (’13:30:45.123′)	1900-01-01 13:30:45.123

2. Converting Dates and Times

In SQL Server, you can convert dates and times from one format to another using the CONVERT function.

The CONVERT function takes a date or time value, followed by the current format, and then the target format. For example:

Table 7: Converting Dates and Times in SQL Server

SQL Query	Output
SELECT CONVERT(varchar, GETDATE(), 101) AS ShortDate	10/15/2021
SELECT CONVERT(varchar, GETDATE(), 113) AS LongDate	15 Oct 2021 13:30:45:123

FAQ

Q1. What is the default date time format in SQL Server?

A1. The default date time format in SQL Server is yyyy-mm-dd hh:mi:ss.mmm.

Q2. What is the difference between datetime and datetime2 data types?

A2. The datetime data type has a precision of up to 3.33 milliseconds, while the datetime2 data type has a higher precision of up to 100 nanoseconds.

Q3. Can I store dates and times in the same column in SQL Server?

A3. Yes, you can store dates and times in the same column using the datetime data type.

Q4. How do I convert a date or time value to a specific format in SQL Server?

A4. You can use the CONVERT function, followed by the current format, and then the target format.

Q5. What is the short date format in SQL Server?

A5. The short date format in SQL Server is dd/mm/yyyy.

Q6. What is the long date format in SQL Server?

A6. The long date format in SQL Server is dd month yyyy.

Q7. What is the time only format in SQL Server?

A7. The time only format in SQL Server is hh:mi:ss.mmm.

Q8. What is the date only format in SQL Server?

A8. The date only format in SQL Server is yyyy-mm-dd.

I hope this article has helped you understand date and time formats in SQL Server. Remember to use the correct format when storing, converting, and displaying dates and times in your SQL queries and operations. Keep coding, Dev!