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
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??


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
Messages: 21146
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;


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

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;


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;


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: Fri Aug 18 03:59:48 CDT 2017

Total time taken to generate the page: 0.15363 seconds