Oracle SQL - Using SysDate to create a specific date

Question

I need to automate a report that will always the first day of the current month and year in the WHERE caluses.

"YYYY-MM-01"

I am trying to achieve the following but sadly, I can't get anything to work using To_Char/ To_Date/SysDate.

    to_date(SYSDATE('YYYY-MM') + '-01', 'yyyy-mm-dd')

Thank you

I don't have an Oracle box here to test but SYSDATE does not accept parameters. You should be getting an error message :-? — Álvaro González
– Álvaro González, Commented Feb 28, 2014 at 16:09
Thanks everyone. Both proposed solution by (Joe / Gordon Linoff) are working as expected. — Alexandre
– Alexandre, Commented Feb 28, 2014 at 17:44

Joe · Accepted Answer · 2014-02-28 16:07:52Z

5

To get the first day of the current month (as a DATE), simply:

trunc(sysdate,'MON')

or, formatted:

to_char(trunc(sysdate,'MON'),'yyyy-mm-dd')

answered Feb 28, 2014 at 16:07

Joe

6,8271 gold badge18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2014-02-28 16:05:31Z

1

The string concatenation operator in Oracle is ||, not +. And SYSDATE is a constant not a function:

to_date(to_char(SYSDATE, 'YYYY-MM') || '-01', 'yyyy-mm-dd')

By the way, you can also do this calculation as:

trunc(sysdate - extract(day from sysdate) + 1)

That is, subtract the day of the month and add one. That way, you don't have to mess with fiddly date/character formats.

answered Feb 28, 2014 at 16:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges