How to Compare Time in MS SQL Server?

To compare time in MS SQL Server, use the comparison operators (=,<,>, etc.). In this article, we will be making use of the Microsoft SQL Server as our database. and comparing times using both pre-defined dates and the current date and time with the GETDATE() function.

First, let’s create a database named “geeks” in MS SQL Server and then compare times.

Query:

Creating Database:

CREATE DATABASE geeks;
USE geeks;

Here, we will create two variables of datetime, that we need to compare.

Compare Time in MS SQL Server

A simple way to compare time MS SQL Server is by using comparison operators. The time should be in datetime format and then it can be compared to each other correctly.

To convert a value into datetime value, use the CONVERT() function.

Syntax:

DECLARE @input1 DATETIME;
DECLARE @input2 DATETIME;

SELECT   @input1 = CONVERT(VARCHAR(20), CONVERT(DATETIME, 'date and time'), 114);
SELECT   @input2 = CONVERT(VARCHAR(20), CONVERT(DATETIME, 'date and time'), 114);

IF @input1 <= @input2
  PRINT '@input1 less then @input2'
ELSE
  PRINT '@input1 more then @input2';

Examples of Comparing Time in MS SQL Server

Let’s look at some examples on how to compare time in MS SQL Server. There are two ways in which we can compare time in MS SQL Server. These are:

1. Using pre-defined date and time.

In MS SQL Server, the query can be designed to compare two or more datetime variables using just the “if” statements.

Example:

DECLARE @day1 DATETIME;
DECLARE @day2 DATETIME;

SELECT   @day1 = CONVERT(VARCHAR(20), CONVERT(DATETIME, '2019-02-11 08:00:00'), 114);
SELECT   @day2 = CONVERT(VARCHAR(20), CONVERT(DATETIME, '2020-02-11 08:00:00'), 114);

IF @day1 <= @day2
  PRINT '@day1 less then @day2'
ELSE
  PRINT '@day1 more then @day2';

Output :

Explanation: In this result, @day1 is less than @day2, so the first condition is met and the corresponding message is printed.

2. Using GETDATE() to compare with current date and time.

The GETDATE() function in SQL can also be used to fetch current date and time, and then it can be compared to a given time value.

Example:

DECLARE @day1 DATETIME;
DECLARE @day2 DATETIME;

SELECT @day1 = CONVERT(VARCHAR(20), GETDATE(), 114);
SELECT @day2 = CONVERT(VARCHAR(20), CONVERT(DATETIME, '2019-02-11 08:00:00'), 114);

IF @day1 <= @day2
    PRINT '@day1 less than @day2'
ELSE
    PRINT '@day1 more than @day2';

Output:

Explanation: In this result, @day1 (current date and time) is more than @day2, so the second condition is met and the corresponding message is printed.

Points to Remember

• Use standard comparison operators (=, <, >, <=, >=, <>) to compare datetime values.
• The CONVERT function is used to change the format of the datetime values for comparison.
• Use GETDATE() to retrieve the current date and time for comparisons.
• The IF statement is used to evaluate conditions and execute corresponding actions.