OraFAQ Forum: Forms » problem in date format

Home » Developer & Programmer » Forms » problem in date format (

) 1 Vote

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	procedures
Next Topic:	query/where box

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 11:44:31 CDT 2024