SQL date part 2

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

Poston Mon, 12 Nov 2007 11:34 am

Hey guys IM needed a SQL again, lol good for macgyver on duty here's the problem I have in a three-column table the first has the second and DTUltimo_recebimento DTvencimento VLjuros. good need to bring everyone in the dtultimo_recebimento > dtvencimento and vljuros is not null until ai beauty he brings everyone who paid with interest but the dates are not treating of Saturdays and Sundays, I wanted a basis to start differentiating between Saturday and Sunday troxesse not type record the dates of Saturday and Sunday only working days I thank you all!
carlos12
Location: rio

Poston Mon, 12 Nov 2007 12:07 pm

Attempts to use the to_char command passing the'd ' format:
Code: Select all
select to_char(sysdate, 'D') from dual;
this command will return a number from 1 to 7, indicating the day of the week (Sunday = 1, Segudna = 2, etc.).
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Mon, 12 Nov 2007 3:14 pm

Brother, beyond the Tip of Rafael, you can also get the complete information of the day, the Bank, as in the following example:
Code: Select all
SELECT TO_CHAR(SYSDATE,'Day')
  FROM dual;
or:
Code: Select all
SELECT TO_CHAR(SYSDATE,'Dy')
  FROM dual;
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Mon, 12 Nov 2007 5:48 pm

Brother, beyond the Tip of Rafael, you can also get the complete information of the day, the Bank, as in the example below: code: SELECT TO_CHAR (SYSDATE, ' Day ') FROM dual;



Or: code: SELECT TO_CHAR (SYSDATE, ' Dy ') FROM dual;
Oops, it's true, cheezburger network.
Except in such cases as the function's return depends on the NLS_LANG parameter and NLS_DATE_LANG (like the formats ' Day ' and ' Dy ' return the names of the days of the week in full and abbreviated respectively), if the session is with any of these parameters in a different language, their comparison could stop working ... for example:
Code: Select all
alter session set nls_date_language=ENGLISH;
select to_char(sysdate, 'Dy') dia_abreviado
     , to_char(sysdate, 'Day') dia_por_extenso
     , to_char(sysdate, 'D') dia_numerico
from dual;

dia_abreviado   dia_por_extenso   dia_numerico
MON             MONDAY            2

alter session set nls_date_language=BRAZILIAN PORTUGUESE;
select to_char(sysdate, 'Dy') dia_abreviado
     , to_char(sysdate, 'Day') dia_por_extenso
     , to_char(sysdate, 'D') dia_numerico
from dual;

dia_abreviado   dia_por_extenso   dia_numerico
SEG             SEGUNDA-FEIRA     2
just to take a little carebecause sometimes you may come across an application that knows that change any of these parameters, and make your logic go down the drain (I say ... lol)
rogenaro
Location: Londrina - PR

Rafael O. Genaro

Poston Mon, 12 Nov 2007 6:38 pm

All right, this really Rafael should be taken.
Thanks for the post.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Tue, 13 Nov 2007 11:41 am

Guys thanks for the tip man helped a lot, once again you guys were awesome thanks so much!!
carlos12
Location: rio


  • See also
    Replies
    Views
    Last Post


    Return to SQL

    Who is online

    Users browsing this forum: Majestic-12 [Bot] and 9 guests