Open In App

How to Write a SQL Query For a Specific Date Range and Date Time?

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

In SQL, some problems require us to retrieve rows based on their dates and times. For such cases, we use the DATETIME2 datatype present in SQL. For this article, we will be using the Microsoft SQL Server as our database.

Note – Here, we will use the WHERE and BETWEEN clauses along with the query to limit our rows to the given time. The pattern of saving date and time in MS SQL Server is yyyy:mm: dd hh:mm: ss. The time is represented in a 24-hour format. The date and time are collectively stored in a column using the datatype DATETIME2.

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 PERSONAL inside the database GeeksForGeeks. This table has 3 columns namely BABY_NAME, WARD_NUMBER, and BIRTH_DATE_TIME containing the name, ward number, and date and time of birth of various babies.

Query:

CREATE TABLE PERSONAL(
BABY_NAME VARCHAR(10),
WARD_NUMBER INT,
BIRTH_DATE_TIME DATETIME2);

Output:

Step 4: Describe the structure of the table PERSONAL.

Query:

EXEC SP_COLUMNS PERSONAL;

Output:

Step 5: Insert 5 rows into the MARKS table.

Query:

INSERT INTO PERSONAL VALUES('TARA',3,'2001-01-10 10:40:50');
INSERT INTO PERSONAL VALUES('ANGEL',4,'2001-03-27 11:00:37');
INSERT INTO PERSONAL VALUES('AYUSH',1,'2002-09-18 13:45:21');
INSERT INTO PERSONAL VALUES('VEER',10,'2005-02-28 21:26:54');
INSERT INTO PERSONAL VALUES('ISHAN',2,'2008-12-25 00:01:00');

Output:

Step 6: Display all the rows of the MARKS table including the 0(zero) values.

Query:

SELECT * FROM PERSONAL;

Output:

Step 7: Retrieve the details of the babies born between 12:00 am, 1st January 2000 and 12:00 pm, 18th September 2002.

Query:

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN
'2000-01-01 00:00:00' AND '2002-09-18 12:00:00';

Output:

Step 8: Retrieve the details of the babies born between 11:00 am, 1st May 2001 and 10:00 pm, 1st May 2005.

Query:

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME BETWEEN
 '2001-03-01 11:00:00' AND '2005-03-01 22:00:00';

Output:

Step 9: Retrieve the details of the babies born on or after the Christmas of 2005.

Query:

SELECT * FROM PERSONAL WHERE BIRTH_DATE_TIME > 
'2005-12-25 00:00:00';

Output:


Last Updated : 28 Oct, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads