Menu
I have a datevariable, I would like to have convert it to first day of its monh,
602
Since SYSDATE provides the current date and time, using it as the date parameter for the TRUNC() function and a MM (Month) format model returns the first day of the month because TRUNC() rounds down. First day of the current month. The last day of the current month.
Here's how this works: First we format the date in YYYYMMDD... format truncating to keep just the 6 leftmost characters in order to keep just the YYYYMM portion, and then append '01' as the month - and voila! you have the first day of the current month.
trunc(sysdate,'MONTH') = first day. last_day(sysdate) = last day.
Here's one way to find the first and last day of the current year: SELECT TRUNC (SYSDATE, 'YEAR') AS first_day, ADD_MONTHS ( TRUNC (SYSDATE, 'YEAR') , 12 ) - 1 AS last_dayFROM dual; You can substitute any DATE expression for SYSDATE to get the first or last day of the year that contains that DATE.
According to http://psoug.org/reference/date_func.html, this should work a dandy...
SELECT TRUNC(yourDateField, 'MONTH') FROM yourTable 
SQL> select to_date('31/07/2010', 'DD/MM/YYYY') from dual; TO_DATE(' --------- 31-JUL-10 SQL> select trunc(to_date('31/07/2010', 'DD/MM/YYYY'), 'MM') from dual; TRUNC(TO_ --------- 01-JUL-10 SQL> If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
