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
Returning date in the format MM / DD / YYYY
- Porva
- 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
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?:
is giving error, I tried to change to_char by to_date but tb does not work!
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 *
FROM tragenda
WHERE TO_CHAR(agen_dt_inicial,'MM/YYYY') = TO_CHAR('11/2007','MM/YYYY'); -- ou SYSDATE
aqui também, enfim...
-
- 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
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Brother , try this way, do a test there:
Whatever, send it there.
SELECT *
FROM TABELA
WHERE TO_CHAR(DAT_EMISSAO,'MM/RRRR') = '11/2007' ;
- dr_gori
- 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
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:
That way everything continues fast
Maybe the ideal neses was to do something like this:
SELECT *
FROM TABELA
WHERE DAT_EMISSAO >= to_date('11/2007','DD/YYYY')
AND DAT_EMISSAO < to_date('12/2007','DD/YYYY')
- Porva
- 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
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:
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 ???
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:
vn_qtd_dias_mês := SUBSTR( (ADD_MONTHS( TRUNC(SYSDATE,'MM'), + 1 ) - 1), 1,2);
There is no "extract_days_month" or something like that ???
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
Araraquara - SP
And there, beleza ??
has a yes. follows there ..
[] 's
has a yes. follows there ..
SQL> SELECT SYSDATE DATA,
2 TO_CHAR(LAST_DAY(SYSDATE),'DD') ULT_DIA
3 FROM DUAL
4 /
DATA UL
--------- --
29-NOV-07 30
- Porva
- 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
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:
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:
Is it possible for me to manipulate a cursor after it is already charged ???
ex, Here I open the cursor with a select any:
OPEN c_(vn_dt_atual, vn_dt_proxima);
LOOP
FETCH c_ INTO reg;
EXIT WHEN c_%NotFound;
END LOOP;
another loop, the cursor will already be loaded, and I will need it already to do comparison tests below:
FOR i IN 1..42 LOOP
--Manipular a variável do cursor já carregada aqui dentro (???)
END LOOP;
-
- Rank: DBA Sênior
- Posts: 365
- Joined: Tue, 24 May 2005 2:24 pm
- Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP
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.
[] 's
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.
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
- Porva
- 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
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.
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.
- Porva
- 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
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:
Now I ask, because the comparison of the field data = to_date ('12 / 2007 ',' dd / yyyy ') does not work (não returns no records!)?
If I do
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?!?!
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 *
FROM TABELA
WHERE DAT_EMISSAO >= to_date('11/2007','DD/YYYY')
AND DAT_EMISSAO < to_date('12/2007','DD/YYYY')
SELECT *
FROM tragenda
WHERE agen_dt_inicial = TO_DATE('12/2007','MM/YYYY')
SELECT *
FROM tragenda
WHERE agen_dt_inicial >= TO_DATE('12/2007','MM/YYYY')
- Porva
- 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
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!
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 "
WHERE TO_DATE(agen_dt_inicial,'MM/YYYY') =
TO_DATE('12/2007','MM/YYYY')
-
- 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
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?
How can you do this test on your base?
SELECT *
FROM TRAGENDA
WHERE TO_DATE(TO_CHAR(AGEN_DT_INICIAL,'MM/RRRR'),'MM/RRRR') = TO_DATE('12/2007','MM/RRRR');
- Porva
- 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
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
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
-
- 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
Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP
Blezera Brother ...
Needing, we are there.
Needing, we are there.
-
- Information
-
Who is online
Users browsing this forum: Bing [Bot] and 12 guests