How to return 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
  

Poston Wed, 03 Oct 2007 11:46 am

How to return date in the format MM/DD/YYYY?

folks, the question sounds stupid but I really don't know, hehe you can do this in the Select or in the own Forms???


Thanks
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Wed, 03 Oct 2007 11:50 am

I got it, thanks!

To_Char (rhpessoa. pess_dt_nascimento, ' MM/DD/YYYY '),
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Wed, 28 Nov 2007 3:10 pm

I took an old topic created by me (so you don't have to create another) because I am again with a question about dates, and searched the Forum, I found what would solve my problem I need to return a Select only those records of the month/year reported in table TRAGENDA I have AGEN_DT_INICIAL field that stores the date and time of the appointment (11/25/2007 15:00 for example), when I get on the screen, it shows a kind of calendar that I developed here, per month how do I select in this case?

ex, catching month and year, 11/2007, bringing all registered records this month/year?:
Code: 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, tried swapping TO_CHAR TO_DATE by but também does not work!
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Wed, 28 Nov 2007 4:05 pm

Brother try this way, do a test here:
Code: Select all

SELECT *
  FROM TABELA
WHERE TO_CHAR(DAT_EMISSAO,'MM/RRRR') = '11/2007'   ;

qualquer thing, says ai.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Thu, 29 Nov 2007 7:54 am

This way works Yes, but remember that as soon as you kill the index.
Perhaps the ideal case of peasants 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')
that way everything continues fast:-D
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 Thu, 29 Nov 2007 9:24 am

guys, thanks for the tips, for while I'm walking here with my calendar/agenda done on race here in the Forms:D now a another question, is there a way more \"elegant\" and more straightforward to know the total number of days in a particular month, without being the way you did here:
Code: Select all
vn_qtd_dias_mes := SUBSTR( (ADD_MONTHS( TRUNC(SYSDATE,'MM'), + 1 ) - 1), 1,2);
took the first day of the month, put 1 more month on it, aí subtracts 1 day : S and then it took a SUBSTR!!!

There is no \"Extract_Days_Month\" or something like that??
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Thu, 29 Nov 2007 9:37 am

And there Proof, beleza?

has a shape. follows ai ...
Code: 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
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Thu, 29 Nov 2007 9:56 am

aeeee guys, what would I do without this forum:D valeuzão aí after it put the image of the parade here, hehe okay crazy to do:-
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Thu, 29 Nov 2007 10:58 am

galera, leveraging the same topic ... can I manipulate a cursor after it is already carreagado???

ex, here I open the cursor with a select any:
Code: Select all
OPEN c_(vn_dt_atual, vn_dt_proxima); 
LOOP
  FETCH c_ INTO reg;     
  EXIT WHEN c_%NotFound;
END LOOP;
can I enter another loop with this cursor already loaded and be able to ' move ' between the headings (lines) of him??? a CURSOR type.NEXT, something, without having used the FETCH again ANOTHER LOOP, the cursor will already be loaded, and I need that he's already to do comparison tests below:
Code: Select all
FOR i IN 1..42 LOOP
   
  --Manipular a variável do cursor já carregada aqui dentro (???)

END LOOP;
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Thu, 29 Nov 2007 11:05 am

And there Proof, beleza?

man, I am unaware of a way to navigate through a cursor, I think that does not exist, but an option would you load your cursor on a temporary table using the fetch into bulck collect ... There is only you use a go go or go back in your temporary table..

about override the value returned in the cursor has how to make yea, here is an example there.

Code: 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
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Fri, 30 Nov 2007 7:30 am

opa Cristiano, thanks for the tip but I was able to assemble the logic using Fetch even the cursor, I didn't know it was possible to manipulate livremento, putting in If conditions, etc..


now it's something else that I'm tangled up here, something apparently beast, but I can't figure out what it is!

I have a list on the screen of the data base not PopList type, a record group to populate this list when the Forms is opened, the list is populated normally but I can not access his records, select the record 2, 3, etc, it always goes back to the first.

the property of both the block list is not quanto data base!

What can be happening?!?!


Select Record Group:
Code: Select all
SELECT veic_nm_nome || ' [' || veic_nr_placa || ']' Label,
       veic_nr_placa Value
  FROM trveiculo
WHERE veic_tp_veiculo IN ('O','R','C','V')
ORDER BY veic_nm_nome
detail, I have another list in another canvas exactly equal, however, created another record group for him, and is working normally, properties are idênttcas!

I saw that there is some restrictions here in POPULATE_LIST pro Help, but I couldn't get this straight!


Usage Notes Do not use the built-in POPULATE_LIST 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 have already been fetched.

For example, assume that the list item contains the values A, B, and C and the Mapping of Other Values property is defined. Assume also that these values have been fetched from the database (the 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 these values were removed from the list and replaced with new values.

Before populating the list, close any open queries. Use the ABORT_QUERY built-in to close an open query.
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Fri, 30 Nov 2007 7:32 am

just correcting the List that is working, is on a block table base, what is base table does not work
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Tue, 04 Dec 2007 9:45 am

I managed to solve the whole list, it was a whole library here which left the block in query mode. : S Gori, you said on another page so I can use that way comparison of interval between dates \"that way works Yes, but remember that as soon as you kill the index.
Perhaps the ideal in these cases was to make something like: code:
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 wonder, why the comparison of field Data = to_date (' 12/2007 ', ' DD/YYYY ') does not work (não returns no registration!)?

Code: Select all
SELECT *
  FROM tragenda
WHERE agen_dt_inicial = TO_DATE('12/2007','MM/YYYY')
if I do
Code: Select all
SELECT *
  FROM tragenda
WHERE agen_dt_inicial >= TO_DATE('12/2007','MM/YYYY')
aí me returns all the records from the month of December, which is this crazy SQL engine that does not accept = date comparisons but if > = accept?!?!
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Tue, 04 Dec 2007 9:50 am

Another thing, on SQL tool (with the mask properly configured: MM/YYYY), if I do it that way, it works!

Code: Select all
WHERE TO_DATE(agen_dt_inicial,'MM/YYYY') =
TO_DATE('12/2007','MM/YYYY')
but if I put the same way in Forms, in time to open the cursor gives the error: \"ORA-01841 (full) year must be between -4713 and +9999, and not to be 0\"
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Tue, 04 Dec 2007 9:54 am

Brother, do this test in your base?
Code: Select all
SELECT *
   FROM TRAGENDA
WHERE  TO_DATE(TO_CHAR(AGEN_DT_INICIAL,'MM/RRRR'),'MM/RRRR') = TO_DATE('12/2007','MM/RRRR');
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Tue, 04 Dec 2007 10:06 am

pow Paulão, funfou BRO!! at the base and at the Cursor, that way you went?

Sorry for the ' ignorance ' guys, is that this whole conversion, especially when working with dates, is something I think well boring!, I'm still learning the possibilities thanks again:)
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP

Poston Tue, 04 Dec 2007 10:13 am

Blezera brother ...

Need, we are there.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Tue, 04 Dec 2007 11:37 am

aí galera, it took some doing but you're practically ready \"calendar\" of the company's vehicles scheduling ...

[img]http%20://img529%20.%20imageshack%20us/.%20img529/agenda2nf7%20./9672%20jpg[/img]
Porva
Location: São Paulo/SP

Rafael S. Nunes
São Paulo/SP


  • See also
    Replies
    Views
    Last Post


      Return to SQL

      Who is online

      Users browsing this forum: No registered users and 2 guests