How to convert date and time format in SQL?
01 Introduction to date and time formats in SQL
Microsoft SQL Server (MSSQL) supports a variety of date and time formats for storing and manipulating date and time values in the database. The following are the most commonly used formats:
YYYY-MM-DD
This format represents the date in the year-month-day format, with YYYY representing the year, MM
representing the month, and DD representing the day.
HH:MM:DD
This format displays the time in the hour-minute-second format, where HH represents the hour in 24-hour
clock format, MM represents the minute, and SS represents the second.
YYYY-MM-DD HH:MM:SS
The date and time are represented in the year-month-day hour-minute-second format, with the date separated
by a space from the time.
YYYY-MM-DDTHH:MM:SS
This is the ISO standard format for representing date and time values, with the "T" separating the date from
the time.
YYYY-MM-DD HH:MM:SS.nnnnnnn
This format represents the date and time to seven decimal places of precision, with the last seven digits
representing fractional seconds.
YYYY-MM-DD HH:MM:SS.mmm
This format represents the date and time with three decimal places of precision, with the last three digits
representing fractional seconds.
MM/DD/YYYY HH:MM:SS
This format represents the date and time in the month/day/year hour:minute:second format, with forward
slashes separating the month, day, and year.
It is important to note that the default date and time format in MSSQL may differ depending on system settings and regional configurations. However, standard date and time formats are always recommended for better compatibility and consistency across different systems and applications.
02 Understanding the different date and time data types
Microsoft SQL Server (MSSQL) supports a variety of data types for storing date and time values, each with its own format and range of values. The following are the most commonly used data types:
DATE
This data type only stores the date in the format YYYY-MM-DD.
The values range from January 1, 0001 to December 31, 9999.
TIME
This data type only stores the time component in the format hh:mm:ss.0000000.
The value range is 00:00:00.0000000 to 23:59:59.9999999.
DATETIME
This data type stores date and time information in the format YYYY-MM-DD hh:mm:ss.000.
The values range from January 1, 1753 to December 31, 9999.
SMALLDATETIME
This data type stores both date and time components in the YYYY-MM-DD hh:mm:ss format.
The values range from January 1, 1900 to June 6, 2079.
DATETIME2
This data type stores date and time components in the format YYYY-MM-DD hh:mm:ss.ffffff.
The values range from January 1, 0001 to December 31, 9999.
DATETIMEOFFSET
This data type stores both date and time components in the following format: YYYY-MM-DD hh:mm:ss.ffffff +hh:mm
or -hh:mm.
The values range from January 1, 0001 to December 31, 9999.
SMALLDATETIME
This data type only stores the time portion with a resolution of one-tenth of a second.
The value range is 00:00:00.0000000 to 23:59:59.9999999.
To ensure accurate representation and manipulation of data when storing and retrieving date and time values in MSSQL, it is critical to understand the data types and their specific formats and ranges.
<script type='text/javascript'>
$('i[rel="pre"]').replaceWith(function() {
return $('<pre><code>' + $(this).html() + '</code></pre>');
});
var pres = document.querySelectorAll('pre,kbd,blockquote');
for (var i = 0; i < pres.length; i++) {
pres[i].addEventListener("dblclick", function () {
var selection = getSelection();
var range = document.createRange();
range.selectNodeContents(this);
selection.removeAllRanges();
selection.addRange(range);
}, false);
}
</script>