How to get the first and last date of the current year?

In SQL, to get the first and last date of the current year, we have to run some queries using date functions like YEAR(), CURDATE(), MAKEDATE(), CONCAT(), or LAST_DAY(), depending on the database system. In this blog, we will learn how to get the first and last date of the current year in detail.

Table of Contents:

• SQL Queries to get the First and Last Date of the Current Year Across Different Databases
  • Query to get the First and Last Date of the Current Year in MySQL
  • Query to get the First and Last Date of the Current Year in PostgreSQL
  • Query to get the First and Last Date of the Current Year in SQL Server
  • Query to get the First and Last Date of the Current Year in Oracle Database
• Example to Get the First and Last Date of the Current Year
• When should we need these Queries?
• Conclusion
• FAQs

SQL Query to get the first and last date of the current year?

In SQL,  to get the first and last date of the current year, we have different methods based on the database:

Let’s have a look at different database systems query:

1. Query to get the First and Last Date of the Current Year in MySQL

SELECT 
    MAKEDATE(YEAR(CURDATE()), 1) AS first_date_of_year,
    MAKEDATE(YEAR(CURDATE()), 365) AS last_date_of_year;

2. Query to get the First and Last Date of the Current Year in PostgreSQL

SELECT 
    CONCAT(YEAR(CURDATE()), '-01-01') AS first_date_of_year,
    CONCAT(YEAR(CURDATE()), '-12-31') AS last_date_of_year;

3. Query to get the First and Last Date of the Current Year in SQL Server

SELECT 
    CAST(YEAR(GETDATE()) AS VARCHAR) + '-01-01' AS first_date_of_year,
    CAST(YEAR(GETDATE()) AS VARCHAR) + '-12-31' AS last_date_of_year;

4. Query to get the First and Last Date of the Current Year in Oracle Database

SELECT TRUNC(SYSDATE, 'YYYY') AS first_date_of_year FROM dual;
SELECT LAST_DAY(TO_DATE('31-12-' || TO_CHAR(SYSDATE, 'YYYY'), 'DD-MM-YYYY')) AS last_date_of_year FROM dual;
   

These queries are used to find the first and last date of the current year in the Database. 

Example to Get the First and Last Date of the Current Year

Suppose, we have a table with the following data:

Table: Employees

Task: We have to find the employee name who Joined on the First and Last Date of the Current Year

Query:

SELECT Employee_Name, Join_Date
FROM Employees
WHERE Join_Date = MAKEDATE(YEAR(CURDATE()), 1)  OR Join_Date = LAST_DAY(MAKEDATE(YEAR(CURDATE()), 365));  

Output:

When should we need these Queries?

These queries are used in the following scenarios:

1. For Reports: These queries are majorly used in finding the yearly reports of sales, employee salaries, or other time-based data like annual performance, yearly revenue, or yearly expenditures.
2. For Data Cleanup: These queries are also used to clean the previous year’s records if records are not important. It helps to keep the database clean and up-to-date.
3. For Scheduling: These queries help us to find the data on a yearly basis, and perform scheduling tasks accordingly. It makes sure that reports, backups, or other time-dependent tasks are triggered at the right time each year.

Conclusion

SQL stands for Structured Query Language. It is used to fetch the data from Relational databases or tables with the help of some queries.

So far in this article, We have learned, how we can get the first and last date of the current year in different database systems. Also, we have learned why should we use such types of queries. If you want to become an SQL expert, you may refer to our SQL course to master it.

FAQs