Date Operations in SQL

Dates play an important role in the data. 

 

Few scenarios where Date and Time are involved with table:

• Whenever a new record gets inserted into the table, we store the date and time of insertion in the same table.
• If the existing record gets updated with new data, then we store the date and time of updation in the same table.
• We store Date of birth information for a customer.
• And few other information where Date is involved.

 

To work with the dates, we should have a good understanding of Date and DateTime data types.

For. e.g. Taking today’s date and subtracting customer’s date of birth to find age. 

 

Let us check some of the important Date aspects.

 

1) Check the data types of any table columns:

You can simply check the Create statement of that table. Create statement contains the column names and type of each column along with the Primary key and indexes (if any) present in the table.

  

Query:

SHOW CREATE TABLE tbl_name;

Output:

CREATE TABLE `Orders` 

( `OrderID` int(11) NOT NULL AUTO_INCREMENT, 

  `CustomerID` int(11) DEFAULT NULL, 

  `EmployeeID` int(11) DEFAULT NULL, 

  `OrderDate` date DEFAULT NULL, 

  `ShipperID` int(11) DEFAULT NULL, 

  PRIMARY KEY (`OrderID`) 

);

2) Get Today’s Date and Current Time:

Current_Date() is the function to get today’s date and Now() is the function to get current date and time.

  

Query:

SELECT Current_Date(), Now();

Output:

3) Add Date and Time to the column:

Date_ADD() is the function to add days, hours or minutes or seconds to the column

Date_SUB() is the function to subtract days, hours or minutes or seconds from the column

  

Query:

SELECT CURRENT_DATE() as Todays_Date, 

       DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) as Yesterdays_Date,

       DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) as Tomorrows_Date,

       NOW() as CurrentTime,

       DATE_SUB(NOW(), INTERVAL 1 HOUR) as Past_1_hour,

       DATE_ADD(NOW(), INTERVAL 3 HOUR) as Later_3_hour,

       DATE_SUB(NOW(), INTERVAL 30 MINUTE) as Past_30_mins;

Output:

 

4) Calculating days difference between two dates:

Suppose a column with name “DOB” in table contains the date of birth of users and you want to calculate the age from dob. In this case, you can use DATEDIFF function which calculates the no. of days between two dates and later it can be divided by 365 to calculate age in years.

 

Query:

SELECT DATEDIFF(NOW(), DOB)/365 as Age;