Applying case when date-CodePudding

I have to set the start date as 01-01-year which would be pulled from the expense date field. I have written the below query

select to_date(extract(year from rpt.expense_date),'yyyy') from rpt

How can I set the date to 01-01-year which would be pulled from above query.

Thanks in advance.

CodePudding user response：

Use TRUNC to truncate to the start of the year:

SELECT TRUNC(expense_date, 'YY') FROM rpt

CodePudding user response：

You can just truncate the date value:

trunc(rpt.expense_date, 'YYYY')

By default the trunc() function truncates to midnight on the specified day, equivalent to trunc(<date>, 'DD'), but you can use other elements.

In your code:

to_date(extract(year from rpt.expense_date),'yyyy')

you are only supplying the year element to to_date(); in that situation the other date elements default to the first day of the current month - so today that would give you June 1st in that year, not January 1st. That's hidden away a bit in the documentation:

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.