Get the difference between dates and calculate salary with MySQL?

Let’s say you need to get the difference between dates (JoiningDate – EndDate) of a month i.e. days to calculate the salary. The daily-wage salary is let’s say 300; therefore for 20 days, it will 6000. In the same way, for 27 days, it will be 8100.

For our example, let us first create a table

mysql> create table DemoTable
   -> (
   -> JoinDate date,
   -> EndDate date
   -> ,
   -> Value int
   -> );
Query OK, 0 rows affected (1.16 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('2019-01-01','2019-01-31',500);
Query OK, 1 row affected (0.37 sec)

mysql> insert into DemoTable values('2019-02-12','2019-02-25',900);
Query OK, 1 row affected (0.22 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

Output

This will produce the following output −

+------------+-------------+-------+
| JoinDate   | EndDate     | Value |
+------------+-------------+-------+
| 2019-01-01 | 2019-01-31  | 500   |
| 2019-02-12 | 2019-02-25  | 900   |
+------------+-------------+-------+
2 rows in set (0.00 sec)

Following is the query to calculate the salary. For date difference, DATEDIFF() is used −

mysql> select ABS(DATEDIFF(JoinDate,EndDate) * Value) AS Total from DemoTable;

Output

This will produce the following output −

+-------+
| Total |
+-------+
| 15000 |
| 11700 |
+-------+
2 rows in set (0.14 sec)

karthikeya Boyini

Updated on: 2020-06-30T11:33:42+05:30

401 Views

Kickstart Your Career

Get certified by completing the course

Get Started