How to Format the Date and Time in the SQL Server

Manipulating the date and time values using the DATETIME, DATE, and TIME data types is one of SQL’s most important functionalities, since these data types are specifically designed to store such values. They are frequently utilized in applications that require the calculation and manipulation of the date and time data. In this article, we will dig deeper into these data types.

Basic DATE Data Types in SQL

• DATE: This data type is used to store the date values in the YYYY-MM-DD format. It is capable of representing the dates within the range of January 1, 0001 to December 31, 9999. The date values are stored in a compact format using only three bytes.
• TIME: The TIME data type is utilized to store the time values in the HH:MI:SS format. It is capable of representing the time values within the range of 00:00:00 to 23:59:59.
• DATETIME: The DATETIME data type is capable of storing both date and time values in the YYYY-MM-DD HH:MI:SS format. It allows the values within the range of January 1, 1753, 00:00:00 to December 31, 9999, 23:59:59 to be stored. This data type is useful for storing the timestamps such as transaction times or event times.
• SMALLDATETIME: This data type is the same as the DATETIME data type but with some smaller difference. It stores both date and time values, but with a smaller range of values from January 1, 1900, 00:00:00 to June 6, 2079, 23:59:59. The values are also rounded to the nearest minute which saves space and reduces the processing time. This data type is useful for storing a time-sensitive information that does not require high precision. It takes only 4 bytes of data. It is stored in the YYYY-MM-DD hh:mm:ss format.
• DATETIME2: This data type is similar to the DATETIME data type but with higher precision and a larger range. The range of values is the same as the DATETIME, but it can store the fractional seconds up to 7 digits.

How Are Dates Stored in the SQL Server?

Within the SQL Server, dates are stored using two 4-byte integers. The initial integer corresponds to the number of days preceding or succeeding the base date of January 1, 1900. The second integer corresponds to the number of 1/300th of a second that elapsed since midnight. For example, the date “January 1, 2000 12:00:00 PM” is stored as the two integers – 36,529 (the number of days between January 1, 1900 and January 1, 2000) and 43,200,000 (the number of 1/300th of a second since midnight).

The SQL Server also provides a number of built-in functions to work with dates, such as DATEADD, DATEDIFF, and CONVERT, which allow us to perform the common operations such as adding or subtracting the time intervals, comparing the dates, and formatting the dates.

To perform the SQL queries on a database, we typically use a tool such as the SQL Server Management Studio (SSMS).

Creating a [dbo].[ORDERS] Table

To create a table in our database, we have to write the following queries:

Inserting the Data into the [dbo].[ORDERS] Table

Insert the values in the table using the following command:

Converting a DATE to STRING

If we bring back the datetime values from the table, the results will show as “2021-10-01 10:30:00.000”. But if we like to convert it into a string in our own format in the SQL code, we can do that using the following function:

Output:

Finding a Specific Date Using the WHERE Clause

To find the orders where the order date is 1st December 2021, we have to follow the following query:

Output:

Separating the DATE from the TIME

We can also use the convert function on a date to retrieve only the date value by specifying a style. For this, we use the following query:

Output:

Getting the Current Date and Time

Here is an example of an SQL query that retrieves the current date and time using the GETDATE and CURRENT_TIMESTAMP functions:

Output:

Finding the Difference Between Two Dates

It’s often helpful to find the difference between two dates. With this, we can do things like find the number of days between the order and a delivery date.

Here’s an example:

Output:

Conclusion

We learned how to manipulate the dates, times, and strings using the built-in functions. These functions are very handy to perform calculations and transformations on the data stored in the SQL Server tables. By understanding and using these data types and functions effectively, we can write more efficient and powerful SQL queries.