First day of 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
Post Reply
User avatar
tfg
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 83
Joined: Mon, 03 May 2004 3:38 pm
Location: Novo Hamburgo - RS

Hello everyone

Do you have any function that looks for the first day of the month?
(I know it's always 1, but I did not want to make a to_char to discover)
Example:

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: -o

Thanks!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

You can use the truncation for this !!!

See the simple example:

Select all

SQL> select trunc(sysdate, 'mm') DATA from dual; 
 
DATA 
--------- 
01-DEC-04 

can also pick up the first day of the year!

Select all

SQL> select trunc(sysdate, 'rr') DATA from dual; 
 
DATA 
--------- 
01-JAN-04 
User avatar
leobbg
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 22
Joined: Fri, 29 Oct 2004 10:25 am
Location: PORTO ALEGRE - RS
Leo BBG Consultor Oracle

Dude, complementing what dr. He said, I have the custom the First_Day function that receives a date and returns the first day of the month, and inside it I usually put the trunc, that our friend has passed.
User avatar
tfg
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 83
Joined: Mon, 03 May 2004 3:38 pm
Location: Novo Hamburgo - RS

leobbg wrote: I have the custom Function First_Day
But precisely, there is no First_Day function ... (at least not in 9i)
User avatar
leobbg
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 22
Joined: Fri, 29 Oct 2004 10:25 am
Location: PORTO ALEGRE - RS
Leo BBG Consultor Oracle

The phrase ended up going wrong ... there I was writing I have the habit of creating the first_day function that receives a date and returns the first day of the month, and inside it I usually put the trunc, that our friend has passed.
schnu
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 110
Joined: Tue, 24 May 2005 9:16 pm
Location: Dongguan - Guangdong - China

Select all

create or replace function first_day(pdata date) return date is 
  Result date; 
begin 
  -- 
  result := last_day(add_months(pdata, -1)) + 1; 
  return(Result); 
  -- 
end first_day;

Back to Oracle
User avatar
anderson
Rank: Analista Júnior
Rank: Analista Júnior
Posts: 75
Joined: Mon, 06 Jun 2005 1:08 pm
Location: Toledo - PR
Contact:
Anderson Nuernberg
---

So também funciona ...


The truncation with the 'mm' parameter returns the first day of the month, as said in previous post ....


mportes
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 13
Joined: Tue, 01 Nov 2005 11:53 pm
Location: São Paulo
Contact:

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

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 
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot] and 16 guests