Returning date in the format MM / DD / YYYY

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

How to return date in mm / dd / yyyy format ???

Personal, the question seems idiot But is that I do not know, hehe

Is it possible to do this in select or forms ???


Thanks
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

I already got it, thanks!

to_char (rhpessoa.pess_dt_national, 'mm / dd / yyyy'),
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

I took advantage of an old topic created by me (not to have to create another) because I am

again with a doubt about dates, and scoured here by the forum, I did not find

to solve my problem


I need to return in a select only the records of the month / year informed in the Table Tragenda
I have the agen_dt_initial field that stores the date and time of scheduling (25/11/2007

15:00 for example), when I enter the screen, it shows a kind of calendar that

Developed here, per month

How do I select in this case?

ex, taking month and year, 11/2007, bring all registers registered in this month / year?:

Select all

 
SELECT * 
   FROM tragenda 
 WHERE TO_CHAR(agen_dt_inicial,'MM/YYYY') = TO_CHAR('11/2007','MM/YYYY'); -- ou SYSDATE  
 
aqui também, enfim... 
is giving error, I tried to change to_char by to_date but tb does not work!
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Brother , try this way, do a test there:

Select all

 
 
SELECT * 
  FROM TABELA 
 WHERE TO_CHAR(DAT_EMISSAO,'MM/RRRR') = '11/2007'	; 
 
Whatever, send it there.
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

That way it works, but remember that you kill the index.
Maybe the ideal neses was to do something like this:

Select all

SELECT * 
FROM TABELA 
WHERE DAT_EMISSAO >= to_date('11/2007','DD/YYYY') 
  AND DAT_EMISSAO <  to_date('12/2007','DD/YYYY')
That way everything continues fast :-D
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Galera, thanks for the tips, for now I'm walking here with my calendar / calendar made in the race here in the forms: D

Now another question, there is a more "elegant" and more direct way know the total days of a given month, without being the way I did here:

Select all

 
vn_qtd_dias_mês := SUBSTR( (ADD_MONTHS( TRUNC(SYSDATE,'MM'), + 1 ) - 1), 1,2); 
I took the first day of the month, some 1 month in it , aí subtraci 1 day: s and then it was still necessary to make a substr !!!

There is no "extract_days_month" or something like that ???
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And there, beleza ??

has a yes. follows there ..

Select all

 
SQL> SELECT SYSDATE DATA, 
  2         TO_CHAR(LAST_DAY(SYSDATE),'DD') ULT_DIA 
  3    FROM DUAL 
  4  / 
 
DATA      UL 
--------- -- 
29-NOV-07 30 
[] 's
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

aeeee galera, what would be of me without this forum: D

Valeusão ??aí

After ready the image of the stop here, hehe

is very crazy to do: -O
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Galera, taking advantage of the same topic ...

Is it possible for me to manipulate a cursor after it is already charged ???

ex, Here I open the cursor with a select any:

Select all

 
OPEN c_(vn_dt_atual, vn_dt_proxima);   
LOOP 
  FETCH c_ INTO reg;  	 
  EXIT WHEN c_%NotFound; 
END LOOP; 
It is possible for me to enter another loop with this cursor already loaded and Power me 'move' between his positions (lines) ??? type a cursor.next, something like that, without being used the fetch again

another loop, the cursor will already be loaded, and I will need it already to do comparison tests below:

Select all

 
FOR i IN 1..42 LOOP 
 	 
  --Manipular a variável do cursor já carregada aqui dentro (???) 
 
END LOOP; 
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

And there, beleza ??

Dude, I do not know a way to navigate between a cursor, I think it does not exist, but an option would be you load your cursor in a table temporarily using fetch into bulck collect .. Ai is only You use a forever to go or go back to your Temporary table.

About overlending the value returned in the cursor has how to do yes, follow an example there.

Select all

 
DECLARE 
   CURSOR C IS 
      SELECT SYSDATE   A FROM DUAL 
      UNION  
      SELECT SYSDATE+1 A FROM DUAL 
      UNION  
      SELECT SYSDATE+2 A FROM DUAL 
      UNION  
      SELECT SYSDATE+3 A FROM DUAL; 
       
BEGIN 
   FOR x IN C LOOP 
      dbms_output.put_line('>> '||X.A); 
   END LOOP; 
   dbms_output.put_line(' - '); 
   FOR x IN C LOOP 
      IF c%ROWCOUNT=2 THEN 
         X.A := SYSDATE+30; 
      END IF; 
      dbms_output.put_line('>> '||X.A); 
   END LOOP; 
END ; 
 
>> 29/11/2007 11:57:06 
>> 30/11/2007 11:57:06 
>> 01/12/2007 11:57:06 
>> 02/12/2007 11:57:06 
 -  
>> 29/11/2007 11:57:06 
>> 29/12/2007 11:57:06 << ACRESCIMO DE 30 DIAS, ROWCOUNT = 2 
>> 01/12/2007 11:57:06 
>> 02/12/2007 11:57:06 
[] 's
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Cristiano, thanks for the tip

but I managed to set up logic using fetch even in the cursor, did not know that it was possible to manipulate frequent, placing inside IF conditions, etc.


Now is something else I'm screwed up here, something seemingly beast, but I can not figure out what it is!

I have a list on the Poplist type screen, no base date, a record group to popular this list when the forms is open, the list is populated normally, but I can not access his records, If I select Registry 2, 3, etc, it always returns pro first!

The property of both the list quanto block is no date base!

What can be happening?!?!


Select of Record Group:



Detail, I have a Another list in another canvas exactly the same, however, I created another record group for him, and is working normally, the properties are identtable!

I saw that there are some restrictions for population_list here in the help, but I could not understand right!


Usage Notes

Do not use the population_list Built-in IF The Mapping of Other Values ??Property is defined and there are queried records in The Block. Doing So May Cause Form Builder To Be Unable to Display Records That has Already Been Fetched.

for example, assumes that a list Item Contains The Values ??A, B, and C and The Mapping of Other Values ??Property is Defined. Assume Also Thesis These Values ??Have Been Fetched from The Database (Query Is Open). AT THIS POINT, IF YOU POPULATE THE LIST USING POPULATE_LIST, AN ERROR Will Occur Because Form Builder Will Attempt to Display The Previously Fetched Values ??(A, B, And C), But Will Be Unable To Because Therse Values ??Were Removed from The List And Replaced with New Values.

Before Populating to List, Close Any Open Queries. Use the abort_query built-in to close an open query.
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

only correcting, the list that is working, is in a table base table,

what is not base table that does not work
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

I was able to solve the LIST bid, it was a library bid here that left the block in consultation mode! : S

Gori, you said on the other page for me to use the interval comparison between dates

"In this way it works yes, but remember that you kill the index.
Maybe the ideal in these case was to do something like this: "

Code:

Select all

SELECT * 
FROM TABELA 
WHERE DAT_EMISSAO >= to_date('11/2007','DD/YYYY') 
  AND DAT_EMISSAO <  to_date('12/2007','DD/YYYY') 
Now I ask, because the comparison of the field data = to_date ('12 / 2007 ',' dd / yyyy ') does not work (não returns no records!)?

Select all

SELECT * 
  FROM tragenda 
 WHERE agen_dt_inicial = TO_DATE('12/2007','MM/YYYY')
If I do

Select all

SELECT * 
  FROM tragenda 
 WHERE agen_dt_inicial >= TO_DATE('12/2007','MM/YYYY')
and returns all the records of the month of December, which Maluco mechanism is this SQL that does not accept = in the date comparisons but if you do> = accepts?!?!
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Another thing, in the SQL tool (with the duly configured mask: mm / yyyy), if I do it this way, it works!

Select all

WHERE TO_DATE(agen_dt_inicial,'MM/YYYY') =  
TO_DATE('12/2007','MM/YYYY')
But if I put the same way in the forms, at the time of opening the cursor gives the error: "ORA-01841 (FULL) YEAR MUST BE BETWEEN -4713 AND +9999, AND NOT TO BE 0 "
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Brother,

How can you do this test on your base?

Select all

 
 SELECT * 
   FROM TRAGENDA 
 WHERE  TO_DATE(TO_CHAR(AGEN_DT_INICIAL,'MM/RRRR'),'MM/RRRR') = TO_DATE('12/2007','MM/RRRR');  
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

Pow Paulão, Funfou Véio !! in the base and in the cursor, that way that you passed ..

Sorry for 'ingorance' galera, is that bid of conversion, especially if working with dates, is one thing I think well boring, I'm still learning the possibilities


Thanks again :)
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

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

Blezera Brother ...

Needing, we are there.
User avatar
Porva
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 342
Joined: Mon, 29 Jan 2007 7:36 am
Location: São Paulo/SP
Rafael S. Nunes
São Paulo/SP

aí Galera, work but is practically ready the "calendar" scheduling company vehicles ...

[IMG [0]]
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Bing [Bot] and 12 guests