Hello Dev, as a developer, you know how important it is to work with dates in your application. SQL Server offers several date and time data types and formats to choose from. However, choosing the right format can be critical for your application’s performance and usability. In this article, we will discuss everything you need to know about SQL Server date format.
Understanding Date and Time Data Types in SQL Server
SQL Server has four date and time data types: datetime, smalldatetime, date, and time. Each data type stores date and/or time with different levels of precision and range.
The datetime Data Type
The datetime data type stores date and time data from January 1, 1753, through December 31, 9999, with an accuracy of 3.33 milliseconds or 1/300th of a second. The syntax for datetime is:
Format |
Example |
Description |
|---|---|---|
‘yyyy-mm-dd hh:mi:ss.mmm’ |
‘2021-08-30 14:30:00.000’ |
Date and time with milliseconds |
To insert a datetime value, you can use either the CONVERT() or the CAST() function. For example:
CONVERT(datetime, ‘2021-08-30 14:30:00.000’, 120)
CAST(‘2021-08-30 14:30:00.000’ AS datetime)
The smalldatetime Data Type
The smalldatetime data type stores date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute. The syntax for smalldatetime is:
Format |
Example |
Description |
|---|---|---|
‘yyyy-mm-dd hh:mi’ |
‘2021-08-30 14:30’ |
Date and time without seconds and milliseconds |
To insert a smalldatetime value, you can use either the CONVERT() or the CAST() function. For example:
CONVERT(smalldatetime, ‘2021-08-30 14:30’, 120)
CAST(‘2021-08-30 14:30’ AS smalldatetime)
The date Data Type
The date data type stores date data from January 1, 0001, through December 31, 9999, with an accuracy of one day. The syntax for date is:
Format |
Example |
Description |
|---|---|---|
‘yyyy-mm-dd’ |
‘2021-08-30’ |
Date without time |
To insert a date value, you can use either the CONVERT() or the CAST() function. For example:
CONVERT(date, ‘2021-08-30’, 120)
CAST(‘2021-08-30’ AS date)
The time Data Type
The time data type stores time data from 00:00:00.0000000 through 23:59:59.9999999, with an accuracy of 100 nanoseconds or 1/10th of a microsecond. The syntax for time is:
Format |
Example |
Description |
|---|---|---|
‘hh:mi:ss.mmmuuunnn’ |
’14:30:00.0000000′ |
Time with nanoseconds |
To insert a time value, you can use either the CONVERT() or the CAST() function. For example:
CONVERT(time, ’14:30:00.0000000′, 120)
CAST(’14:30:00.0000000′ AS time)
Formatting Date and Time Strings in SQL Server
SQL Server provides several built-in functions that allow you to format date and time strings according to your requirements.
The CONVERT() Function
The CONVERT() function converts a value from one data type to another data type. It also allows you to format the date and time strings using different styles.
The syntax for CONVERT() is:
CONVERT(data_type, expression [, style])
where:
- data_type: The target data type.
- expression: The value to be converted.
- style: The style code for formatting the date and time strings (optional).
The following table shows some of the commonly used style codes:
Code |
Description |
Example |
|---|---|---|
100 |
Default format for datetime or smalldatetime |
‘Aug 30 20212:30PM’ |
101 |
U.S. format (mm/dd/yyyy) |
’08/30/2021′ |
102 |
ANSI format (yyyy.mm.dd) |
‘2021.08.30’ |
103 |
British/French format (dd/mm/yyyy) |
’30/08/2021′ |
104 |
German format (dd.mm.yyyy) |
‘30.08.2021’ |
105 |
Italian format (dd-mm-yyyy) |
’30-08-2021′ |
106 |
Spanish format (dd mon yyyy, hh:mm:ss) |
’30 Ago 2021, 14:30:00′ |
107 |
Standard format with century (mmm dd, yyyy) |
‘Aug 30, 2021’ |
108 |
Default time format (hh:mm:ss) |
’14:30:00′ |
109 |
Default format with milliseconds (mm-dd-yyyy hh:mi:ss.mmm) |
’08-30-2021 14:30:00.000′ |
110 |
U.S. format with century (mm-dd-yyyy) |
’08-30-2021′ |
111 |
Japan format (yyyy/mm/dd) |
‘2021/08/30’ |
112 |
ISO format (yyyymmdd) |
‘20210830’ |
For example, to display the current datetime in the ISO format, you can use the following query:
SELECT CONVERT(varchar(8), GETDATE(), 112) + ‘ ‘ + CONVERT(varchar(8), GETDATE(),108)
The FORMAT() Function
The FORMAT() function formats the specified value according to a specified format string. It allows you to specify custom format strings for date and time values.
The syntax for FORMAT() is:
FORMAT(value, format [, culture])
where:
- value: The value to be formatted.
- format: The format string for formatting the value.
- culture: The culture identifier for formatting the value (optional).
The following table shows some of the commonly used format strings for date and time values:
String |
Description |
|---|---|
‘d’ |
Short date pattern (default: MM/dd/yyyy) |
‘D’ |
Long date pattern (default: dddd, MMMM dd, yyyy) |
‘t’ |
Short time pattern (default: h:mm tt) |
‘T’ |
Long time pattern (default: h:mm:ss tt) |
‘f’ |
Full date/time pattern (short time) (default: dddd, MMMM dd, yyyy h:mm tt) |
‘F’ |
Full date/time pattern (long time) (default: dddd, MMMM dd, yyyy h:mm:ss tt) |
‘g’ |
General date/time pattern (short time) (default: MM/dd/yyyy h:mm tt) |
‘G’ |
General date/time pattern (long time) (default: MM/dd/yyyy h:mm:ss tt) |
‘s’ |
Sortable date/time pattern (default: yyyy-MM-ddTHH:mm:ss) |
‘u’ |
Universal sortable date/time pattern (default: yyyy-MM-dd HH:mm:ssZ) |
‘U’ |
Full date/time pattern (universal sortable) (default: dddd, MMMM dd, yyyy HH:mm:ssZ) |
‘y’ |
Year month pattern (default: MMMM, yyyy) |
For example, to display the current datetime in the ISO format using the FORMAT() function, you can use the following query:
SELECT FORMAT(GETDATE(), ‘yyyyMMdd HH:mm:ss’)
FAQ: Frequently Asked Questions About SQL Server Date Format
Q: How do I change the date and time format for my SQL Server instance?
A: You can change the default date and time format for your SQL Server instance by setting the ‘dateformat’ and ‘language’ options using the sp_configure system stored procedure. For example:
EXEC sp_configure ‘dateformat’, 3
RECONFIGURE WITH OVERRIDE
This will set the date format to ‘British/French (dd/mm/yy)’. You can find a list of supported language codes in the sys.syslanguages system view.
Q: Why do I get truncation errors when inserting a datetime value?
A: Truncation errors occur when you try to insert a datetime value that is too large for the data type you are using. For example, if you try to insert a datetime value with seconds or milliseconds into a smalldatetime column, you will get a truncation error. To avoid truncation errors, make sure you use the appropriate data type for your date and time values.
Q: How do I get the current date and time in SQL Server?
A: You can get the current date and time in SQL Server using the GETDATE() function. For example:
SELECT GETDATE()
Q: How do I calculate the difference between two datetime values in SQL Server?
A: You can calculate the difference between two datetime values in SQL Server using the DATEDIFF() function. For example:
SELECT DATEDIFF(day, ‘2021-08-30 00:00:00.000’, GETDATE())
This will return the number of days between the specified date and the current date.
Q: Can I use custom date and time formats in SQL Server?
A: Yes, you can use custom date and time formats in SQL Server using the FORMAT() function. For example:
SELECT FORMAT(GETDATE(), ‘yyyy/MM/dd’)
This will display the current date in the ‘yyyy/MM/dd’ format.
Conclusion
Working with dates and times is an essential part of any application development. SQL Server provides a range of data types and formats to choose from, depending on your requirements. By understanding the different data types and formats available, and the built-in functions for formatting date and time strings, you can ensure that your application is performing at its best and providing an excellent user experience.