MONTH in 9i

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Wed, 06 Feb 2008 3:44 pm

Guys, good afternoon!!

I wish you would help me, I need to get always the latest 6 months from a current month that is dynamic, but I'm having some difficulty using ORACLE 9i.

select to_char (add_months (to_date (max (date), ' YYYYMM '), -6), ' YYYYMM ') FROM Tabela_tempo but is not returning a correct value.

So I tried the following.

select data FROM tabela_tempo where month (date) between (select max (month (date)) from tabela_tempo) and (select max (month (date)) -6 from Tabela_tempo)) running this second, pops up the following error ...

ORA-00904: \"MONTH\": invalid identifier could someone help me?!?!?!

valeu
todopoderoso
Location: sao paulo - sp

Poston Wed, 06 Feb 2008 3:48 pm

month (date) I don't know if this is true it would have to be so trunc (date, ' month ')
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Poston Wed, 06 Feb 2008 4:08 pm

Dude, thanks for the tip more unfortunately didn't work ... displays the following error: ORA-01722: invalid number gives a look like: select data FROM tabela_tempo where trunc (date, ' month ') between (select max (trunc (date, ' month ')) from tabela_tempo) and (select max (trunc (date, ' month ')) -6 from tabela_tempo)
todopoderoso
Location: sao paulo - sp

Poston Wed, 06 Feb 2008 4:08 pm

Brother, would that be?
Code: Select all
SELECT ADD_MONTHS(SYSDATE,-6)
  FROM  dual;
only treat the SYSDATE by parameter that will pass.
Trevisolli
Location: Araraquara - SP

Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Poston Thu, 07 Feb 2008 11:41 am

Following this your date field is of type date..?


and also would have to make a change to the script
Code: Select all
select data FROM tabela_tempo where trunc(data, 'month') between (select max(trunc(data, 'month')) from tabela_tempo) and ADD_MONTHS((select max(trunc(data, 'month')) from tabela_tempo),-6)
If your DATA field is of type date this select should work now if not you will have to use the to_date inside the trunc vlw
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Poston Thu, 07 Feb 2008 11:43 am

improving the select can prevent the use of between see
Code: Select all
select data
FROM tabela_tempo
where trunc(data, 'month') >= ADD_MONTHS((select max(trunc(data, 'month')) from tabela_tempo),-6)
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas



Return to SQL

Who is online

Users browsing this forum: No registered users and 4 guests