Home » Developer & Programmer » Forms » problem in date format  () 1 Vote
problem in date format [message #185039] Sun, 30 July 2006 08:37 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

Hii all

can anyone help me in this
i display 'vpay_date' on screen by using the format 'DD_MM_YYYY'
when it is displayed it is in that format 'DD-MON-YYYY'
this is lien code for this changing format
is there any something wrong in it??


 vPAY_DATE := TO_DATE (vPAY_DATE,'DD-MM-YYYY');



thanks for everyone helped and helping me
Re: problem in date format [message #185049 is a reply to message #185039] Sun, 30 July 2006 12:26 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the datatype of the "vpay_date" table column? What is the datatype of this column in your form?

Dates should be stored into DATE columns, not VARCHAR2 ones.

How will it be displayed and presented to the end-user, depends on business requirements. If form column is of a DATE datatype, it should have one of acceptable formats. If it is CHARACTER one, format it as you wish. This is SQL*Plus example, but will show you the difference: first, create a table and insert some data into it:
SQL> create table test (dat_char varchar2(20), dat_date date);

Table created.

SQL> insert into test values
  2  ('30.07.2006', to_date('30.07.2006', 'dd.mm.yyyy'));

1 row created.

SQL> select * from test;

DAT_CHAR             DAT_DATE
-------------------- --------
30.07.2006           30.07.06
Now select DATE column and format it as you wish:
SQL> select to_char(dat_date, 'dd-mm-yyyy') from test;

TO_CHAR(DA
----------
30-07-2006

SQL> select to_char(dat_date, 'dd#$mm!?yyyy') from test;

TO_CHAR(DAT_
------------
30#$07!?2006
What happens to the CHAR column?
SQL> select to_char(dat_char, 'dd___mm___yyyy') from test;
select to_char(dat_char, 'dd___mm___yyyy') from test
               *
ERROR at line 1:
ORA-01722: invalid number


SQL> select to_date(dat_char, 'dd___mm___yyyy') from test;

TO_DATE(
--------
30.07.06

SQL>
The last line (30.07.06) shows default NLS_DATE_FORMAT setting. It can be changed in a way you want it; for example
SQL> alter session set nls_date_Format = "dd+mm+yyyy";

Session altered.

SQL> select to_date(dat_char, 'dd___mm___yyyy') from test;

TO_DATE(DA
----------
30+07+2006

SQL>
Therefore, either make your form field CHARACTER and format it as you wish, or change NLS_DATE_FORMAT in order to change default date appearance.

Check this Ask Tom NLS_DATE_FORMAT page for more details.
Previous Topic: procedures
Next Topic: query/where box
Goto Forum:
  


Current Time: Thu Dec 08 00:14:24 CST 2016

Total time taken to generate the page: 0.23890 seconds