How to Select Data Between Two Dates and Times in SQL Server?

In SQL, some transactions need to be extracted based on their completion times and dates. Here, the DATETIME2 data type in SQL to perform such operations. For this article, we will be using the Microsoft SQL Server as our database.

Note: Here, we will use the two dates and times given in the query and separate them using the BETWEEN keyword. This is preceded by the WHERE keyword for satisfying the condition generated by the BETWEEN clause. As stated above, the format of date and time in our table shall be yyyy:mm: dd hh:mm: ss which is implied by DATETIME2. The time is in a 24-hour format.

Syntax:

SELECT * FROM TABLE_NAME WHERE
DATE_TIME_COLUMN BETWEEN 
'STARTING_DATE_TIME' AND 'ENDING_DATE_TIME';

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table ATM inside the database GeeksForGeeks. This table has 3 columns namely HOLDER_NAME, WITHDRAWAL_AMOUNT, and TRANSACTION_TIME containing the name of the account holder, the amount he/she withdrew, and the date and time of the transaction.

Query:

CREATE TABLE ATM(
HOLDER_NAME VARCHAR(10),
WITHDRAWAL_AMOUNT INT,
TRANSACTION_TIME DATETIME2);

Output:

Step 4: Describe the structure of the table ATM.

Query:

EXEC SP_COLUMNS ATM;

Output:

Step 5: Insert 5 rows into the ATM table.

Query:

INSERT INTO ATM VALUES('BOB',300,'2001-01-10 10:40:50');
INSERT INTO ATM VALUES('MARY',400,'2001-03-27 11:00:37');
INSERT INTO ATM VALUES('VANCE',100,'2002-09-18 13:45:21');
INSERT INTO ATM VALUES('OSCAR',1000,'2005-02-28 21:26:54');
INSERT INTO ATM VALUES('PETER',200,'2008-12-25 00:01:00');

Output:

Step 6: Display all the rows of the ATM table.

Query:

SELECT * FROM ATM;

Output:

Step 7: Retrieve the details of the transactions done between 10:00 am, 1st February 2001 and 10:00 pm, 1st May 2007.

Query:

SELECT * FROM ATM WHERE 
TRANSACTION_TIME BETWEEN
'2001-02-01 10:00:00' AND
 '2007-03-01 22:00:00';

Output:

Step 8: Retrieve the details of the transactions done between 09:00 pm, 28th February 2005 and 12:00 am, 25th December 2008.

Query:

SELECT * FROM ATM WHERE TRANSACTION_TIME
BETWEEN '2005-02-28 21:00:00' 
AND '2008-12-25 00:00:00';

Note - The 5th tuple is not displayed as its transaction time is 00:01:00 whereas our query has limited the time to 00:00:00.

Output:

Step 9: Retrieve the details of the transactions done between 10:30 am, 10th January 2001, and 12:00 pm, 27th March 2001.

Query:

SELECT * FROM ATM WHERE TRANSACTION_TIME
BETWEEN '2001-01-10 10:30:00' 
AND '2001-03-27 12:00:00';

Output: