problem in date format [message #185039] |
Sun, 30 July 2006 08:37 |
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 |
|
Littlefoot
Messages: 21818 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 exampleSQL> 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.
|
|
|