MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE employee (id INTEGER PRIMARY KEY AUTO_INCREMENT, date DATE);
INSERT INTO employee (date) VALUES
  ('2022-08-02'), 
  ('2022-08-15'), 
  ('2022-08-08'), 
  ('2022-08-22'), 
  ('2022-08-11'), 
  ('2022-08-31'), 
  ('2022-08-12');

Records: 7  Duplicates: 0  Warnings: 0


SELECT *
FROM employee 
ORDER BY date <= CURRENT_DATE,
         ABS(DATEDIFF(date, CURRENT_DATE));


id
date


6
2022-08-31

4
2022-08-22

2
2022-08-15

7
2022-08-12

5
2022-08-11

3
2022-08-08

1
2022-08-02

id	date
6	2022-08-31
4	2022-08-22
2	2022-08-15
7	2022-08-12
5	2022-08-11
3	2022-08-08
1	2022-08-02