MS SQL Server

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:

CREATE TABLE [dbo].[ORDERS] (
    [ord_number]         INT NOT NULL
     ,[ord_datetime]       DATETIME NULL
      ,[ord_date]           DATE NULL
       ,[deli_datetime]    DATETIME NULL
       ,[last_visit_date]       DATE NULL
    )

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

Insert the values in the table using the following command:

INSERT INTO [dbo].[ORDERS] ([ord_number], [ord_datetime], [ord_date], [deli_datetime], [last_visit_date])
VALUES  
    ('1','2021-10-01 10:30:00', '2021-10-01', '2021-10-12 10:11:10', '2021-11-01'),
    ('2','2021-10-01 12:45:00', '2021-10-01', '2021-10-10 14:10:11', '2021-10-15'),
    ('3','2021-10-30 13:25:00', '2021-10-30', '2021-11-05 13:22:12', '2021-12-30'),
    ('4','2021-09-22 16:30:00', '2021-09-22', '2021-09-25 14:12:13', '2021-12-22'),
    ('5','2021-01-10 18:15:00', '2021-01-10', '2021-02-01 11:15:14', '2022-01-10'),
    ('6',CURRENT_TIMESTAMP, CONVERT(DATE, CURRENT_TIMESTAMP), DATEADD(DAY, 5, CURRENT_TIMESTAMP), DATEADD(MONTH, 5, CURRENT_TIMESTAMP))

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:

SELECT
     [ord_datetime]
    ,CONVERT(VARCHAR(20), [ord_datetime], 22) 'ORDER_DATE_STRING'
FROM
    [dbo].[ORDERS]

Output:

ord_datetime                             ORDER_DATE_STRING
2021-10-01 10:30:00.000 10/01/21 10:30:00 AM
2021-10-01 12:45:00.000 10/01/21 12:45:00 PM
2021-10-30 13:25:00.000 10/30/21  1:25:00 PM

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:

SELECT
    [ord_number]
    ,[ord_date]
    FROM
 [dbo].[ORDERS]
    WHERE
    [ord_date] = '01/10/2021'

Output:

ord_number         ord_date
5                              2021-01-10
5                              2021-01-10

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:

DECLARE @dd AS DATETIME
SET @dd = '2011-07-03 20:30:40'
SELECT CONVERT(VARCHAR , @dd, 103)

Output:

    (No column name)
1   03/07/2011

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:

SELECT
     GETDATE()
        ,CURRENT_TIMESTAMP

Output:

    (No column name)                  (No column name)
1   2023-03-04 23:44:02.883       2023-03-04 23:44:02.883

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:

SELECT
         [ord_date]
        ,[deli_datetime]
        ,DATEDIFF(day, [ord_date], [deli_datetime]) 'del_days'
    FROM
        [dbo].[ORDERS]

Output:

    ord_date       deli_datetime                           del_days
1  2021-10-01    2021-10-12 10:11:10.000    11
2  2021-10-01    2021-10-10 14:10:11.000    9
3  2021-10-30    2021-11-05 13:22:12.000    6

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.

About the author

Bamdeb Ghosh

Bamdeb Ghosh is having hands-on experience in Wireless networking domain.He's an expert in Wireshark capture analysis on Wireless or Wired Networking along with knowledge of Android, Bluetooth, Linux commands and python. Follow his site: wifisharks.com