30

I have a datevariable, I would like to have convert it to first day of its monh,

  • Eg: 10/10/2010 -> 01/10/2010
  • Eg: 31/07/2010 -> 01/07/2010
MatBailie
  • 83,401
  • 18
  • 103
  • 137
Bilgin Kılıç
  • 8,707
  • 14
  • 41
  • 67

6 Answers6

58

According to http://psoug.org/reference/date_func.html, this should work a dandy...

SELECT TRUNC(yourDateField, 'MONTH') FROM yourTable
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • 3
    As @ ErwinBrandstetter points out, it's in the documentation. No need to link to to unofficial sites. – APC Oct 21 '11 at 15:08
  • 1
    I linked the first match that I got when I googled it. Being that it provides the answer, I don't see that as even remotely problematic. – MatBailie Oct 21 '11 at 15:31
  • 2
    The PSOUG site used to be a reputable site when Dan Morgan maintained it. That is no longer the case. Not that it now contains false information, just that it's not being kept up to date. In my opinion it is better to reference the official documentation rather than a site of uncertain provenance, even if it is the first hit in a Google Search. Google Searches throw up all sorts of things. – APC Oct 21 '11 at 16:06
9
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>
BQ.
  • 9,393
  • 3
  • 25
  • 35
9
select trunc(sysdate, 'mm') from dual;
schurik
  • 7,798
  • 2
  • 23
  • 29
3

try this one


select trunc(sysdate, 'MM')firstday , trunc(last_DAY(sysdate)) lastday from dual;
Ashish4434
  • 118
  • 6
2
SELECT trunc(to_date('22-AUG-03'), 'MON') FROM dual;

More in the manual.
About Oracle needing a dummy FROM: Select without a FROM clause in Oracle

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Here is a good example:

select trunc(to_date('15.11.2019', 'DD.MM.YYYY'), 'MONTH') from dual;
westman379
  • 493
  • 1
  • 8
  • 25