First day of the month

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Tue, 28 Dec 2004 10:00 am

Hello guys have any function that fetches the first day of the month?
(I know it is always 1, but I didn't want to do each time a TO_CHAR to find out)

Example:
Code: Select all
SQL> select to_date( '01/'||to_char(sysdate, 'mm/yyyy')   , 'dd/mm/yyyy')
  2  from dual;

TO_DATE('
---------
01-DEC-04

1 row selected.

SQL>
I wanted something like LAST_DAY:-thanks!
tfg
Location: Novo Hamburgo - RS

Poston Tue, 28 Dec 2004 10:07 am

Can use the TRUNC for that!!!

See the simple example:
Code: Select all
SQL> select trunc(sysdate, 'mm') DATA from dual;

DATA
---------
01-DEC-04
can also take the first day of the year!
Code: Select all
SQL> select trunc(sysdate, 'rr') DATA from dual;

DATA
---------
01-JAN-04
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Wed, 05 Jan 2005 12:29 pm

Man, complementing what the Dr. said I have the usual function First_day that receives a date and returns the first day of the month, and within it usually put the trunc, that our friend has passed.
leobbg
Location: PORTO ALEGRE - RS

Leo BBG Consultor Oracle

Poston Wed, 05 Jan 2005 12:40 pm

[quote = " leobbg "] I have the usual function First_day [/quote] but, there is no function FIRST_DAY. (at least not in 9i)
tfg
Location: Novo Hamburgo - RS

Poston Wed, 05 Jan 2005 1:19 pm

the phrase ended up coming out wrong ... There I was writing I have the habit of creating function First_day that receives a date and returns the first day of the month, and within it usually put the trunc, that our friend has passed.
leobbg
Location: PORTO ALEGRE - RS

Leo BBG Consultor Oracle

Poston Thu, 27 Oct 2005 10:58 pm

Code: Select all
create or replace function ( first_day pdata date return date is ) Result date;
begin--result = : ( ( add_months last_day pdata, -1 ) ) + 1;
  return ( Result );
  --end first_day;
back to oracle
schnu
Location: Dongguan - Guangdong - China

Poston Fri, 28 Oct 2005 12:07 pm

so também fünf ...

Code: Select all
create or replace function first_day(pdata date) return date is
  result date;
begin
  --
  result := trunc(pdata,'MM');
  return(result);
  --
end first_day;
the trunc with the parameter ' MM ' returns the first day of the month, as stated in the previous post ....

[] ´ s
anderson
Location: Toledo - PR

Anderson Nuernberg
---

Poston Wed, 02 Nov 2005 12:40 am

Before considering the use of a function in a realistic system, I suggest making a comparison. The test I did was pretty simple, but I believe that it is enough to show that the use of a function in this case would be detrimental to performance.
See example, performed on a Linux RH with version 10 g.
with the use of the function it took 22 seconds to read 1 million and without, that is, only with the TRUNC ()- seconds 6. Considerable difference.

Code: Select all
ops$marcio@LNX10GR2> create table t as
  2  with x as
  3  (
  4   select trunc(sysdate)+ (level-1) dt
  5     from dual
  6    connect by 1=1 and level <= 1000000
  7  )
  8  select * from x
  9  /

Table created.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> select count(*) from t;

     COUNT(*)
-------------
      1000000

1 row selected.

ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> create or replace function f ( p_date in date )
  2  return date as
  3  begin
  4     return trunc(p_date);
  5  end;
  6  /

Function created.

ops$marcio@LNX10GR2> show error
No errors.
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> set timing on
ops$marcio@LNX10GR2> set autotrace traceonly
ops$marcio@LNX10GR2>
ops$marcio@LNX10GR2> select f(dt) from t;

1000000 rows selected.

Elapsed: 00:00:22.21

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      11804  consistent gets
          0  physical reads
          0  redo size
   10300705  bytes sent via SQL*Net to client
     110374  bytes received via SQL*Net from client
      10001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

ops$marcio@LNX10GR2> select trunc(dt) from t;

1000000 rows selected.

Elapsed: 00:00:06.81

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T    |
----------------------------------

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      11804  consistent gets
          0  physical reads
          0  redo size
   10300709  bytes sent via SQL*Net to client
     110374  bytes received via SQL*Net from client
      10001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed
mportes
Location: São Paulo



  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 7 guests