SQL – SELECT DATE

In Microsoft SQL Server, SELECT DATE is used to get the data from the table related to the date, the default format of date is ‘YYYY-MM-DD’.

Syntax:

SELECT * FROM table_name

WHERE condition1, condition2,..;

Now we will execute queries on SELECT DATE on database student in detail step-by-step:

Step 1: Creating a database university:

CREATE DATABASE student;

Step 2: Using the database student:

USE student;

Step 3: Creating a table assignment_details with 5 columns:

In MSSQL there is data type DATE which accepts date.

CREATE TABLE assignment_details
(
    stu_id VARCHAR(20),
    stu_name VARCHAR(20),
    assn_name VARCHAR(20),
    due_date DATE,
    submission_date DATE
);

Step 4: Adding rows into table assignment_details :

INSERT INTO assignment_details VALUES
('191021','SUJITH','DBMS','2021-04-21','2021-04-25'),
('191022','SUDEEP','DBMS','2021-04-21','2021-04-20'),
('191023','SARASWAT','O.S','2021-04-22','2021-04-24'),
('191024','SATWIK','C++','2021-04-23','2021-04-19'),
('191025','KOUSHIK','C++','2021-04-23','2021-04-21'),
('191026','ABHISHEK','O.S','2021-04-22','2021-04-20');

Step 5: Viewing the rows in the table:

SELECT * FROM assignment_details;

Query 1:  Display students who have submitted on ‘2021-04-20’.

SELECT * FROM assignment_details
WHERE submission_date='2021-04-20';

Query 2: Display students who have submitted after duedate.

SELECT * FROM assignment_details
WHERE due_date<submission_date;

Query 3: Display students who have submitted before duedate.

SELECT * FROM assignment_details
WHERE due_date>=submission_date;

Query 4: Display students who have submitted O.S assignment on time.

SELECT stu_id,stu_name,submission_date FROM assignment_details
WHERE assn_name='O.S' AND due_date>=submission_date;