problems with SYSDATE

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Sat, 01 Apr 2006 6:23 pm

Personally, I'd like some suggestions for the following problem, I was wondering which PL/SQL routine should I proceed, my problem is that I do a routine that I take a City Bank and SYSDATE and at the end I have the following string, for example, if the city is são PAULO .01/04/2006 ... .... I have as a result: São Paulo .01 April 2006 realize that the first problem is that the city are registered all in uppercase in BD, to make this STring I had to only put in uppercase the first character (so you could use only UPPER) the second problem is to separate the SYSDATE, the day, month and year, and then the Month should be written in words in standard English ... anyway.Anyway I can only imagine the solution more trivial to be checking out month by month and according to the value put the String on it (if it is equal to 01 puts \"January\", if it is equal to 02 would be \"February\" and so on) but there may be a better way ... ... ... ...

Could someone give me a hand, please?
HumbertoJr
Location: CE

Poston Tue, 04 Apr 2006 10:55 am

the camando initcap puts just the initials in uppercase.

Code: Select all
SELECT initcap('SÃO PAULO, ')||
            TO_CHAR(SYSDATE, 'DD')||' de '||
            TO_CHAR(SYSDATE, 'Month')||'de '||
            TO_CHAR(SYSDATE, 'YYYY') AS RESULTADO
  FROM DUAL;


:o
mello
Location: sp

Poston Tue, 04 Apr 2006 12:19 pm

Hello Mello His command is perfect, but if the Bank has to date in English, the date will appear in English.

Below, I leave the solution to do this:
Code: Select all
SQL> select to_char(sysdate, 'Month') from dual;

TO_CHAR(S
---------
April

SQL>
SQL> SELECT
  2    INITCAP('PORTO ALEGRE')||', '||
  3    TO_CHAR(SYSDATE, 'DD') ||' de '||
  4    RTRIM(TO_CHAR(SYSDATE, 'Month' , 'NLS_DATE_LANGUAGE=PORTUGUESE' )) ||' de '||
  5    TO_CHAR(SYSDATE, 'YYYY')||'.'   HOJE
  6  FROM DUAL
  7  /

HOJE
--------------------------------------
Porto Alegre, 04 de Abril de 2006.

SQL>
Note that even the configuration of my Bank being in English, the date comes out right!

Here on the forum has already been spoken about dates: take a look at this link: http://www.glufke.net/oracle/viewtopic.php?t=22:-
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


  • See also
    Replies
    Views
    Last Post


Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 8 guests