CODEWITHSUNDEEP
sqloracle-database
Abinnaya
Abinnaya

Reputation: 223

how to get the 1st day of the year sql query oracle

I need a sql query to get a 1st day of the 1st month of the year which i am passing.

ex: If i am passing 2021, my output should be 01-JAN-2021 If i am passing 2020, my output should be 01-JAN-2020

Upvotes: 2

Views: 1683

Answers (3)

Sandeep Kumar
Sandeep Kumar

Reputation: 64

Select trunc(sysdate,'YEAR') from dual;

Upvotes: 1

Andrei Odegov
Andrei Odegov

Reputation: 3439

If you pass the year as a number, you can get the desired result with the following code:

with yy(yy) as (
  select 2021 from dual union all
  select 2020 from dual union all
  select 1970 from dual
)
select
  add_months(date'0000-01-01', yy * 12) y1,
  date'0000-01-01' + TO_YMINTERVAL(to_char(yy)||'-0') y2
from yy;

Result:

+------------+------------+
|     Y1     |     Y2     |
+------------+------------+
| 2021-01-01 | 2021-01-01 |
| 2020-01-01 | 2020-01-01 |
| 1970-01-01 | 1970-01-01 |
+------------+------------+

db<>fiddle

Upvotes: 1

MT0
MT0

Reputation: 168232

Assuming you are passing the year as the bind variable :year then:

SELECT TO_DATE(:year||'0101','YYYYMMDD')
FROM   DUAL;

or

SELECT TRUNC(TO_DATE(:year,'YYYY'),'YY')
FROM   DUAL;

Upvotes: 1

Related Questions