Home » SQL & PL/SQL » SQL & PL/SQL » Number in date column (oracle 9i)
Number in date column [message #337594] Thu, 31 July 2008 06:44 Go to next message
veljobs
Messages: 6
Registered: July 2008
Junior Member
Hi,

Is there a way we could insert number in a date column ?

Thanks in advance
vel
Re: Number in date column [message #337596 is a reply to message #337594] Thu, 31 July 2008 06:45 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
NO. Why would you want to do this?
Re: Number in date column [message #337601 is a reply to message #337596] Thu, 31 July 2008 06:58 Go to previous messageGo to next message
veljobs
Messages: 6
Registered: July 2008
Junior Member
I have a requirement which says for a particular date , I have to insert 0 into the date column
Re: Number in date column [message #337602 is a reply to message #337594] Thu, 31 July 2008 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there a way to insert a date in a number column?

Yes, convert all columns to varchar2 or raw then you can insert what you want.

Regards
Michel

[Updated on: Thu, 31 July 2008 07:00]

Report message to a moderator

Re: Number in date column [message #337604 is a reply to message #337602] Thu, 31 July 2008 07:01 Go to previous messageGo to next message
veljobs
Messages: 6
Registered: July 2008
Junior Member
we cant insert a date into a number field ....I was curious to know if there is any way we could insert number in a date field
Re: Number in date column [message #337611 is a reply to message #337601] Thu, 31 July 2008 07:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68707
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
veljobs wrote on Thu, 31 July 2008 13:58
I have a requirement which says for a particular date , I have to insert 0 into the date column

If the requirement st... sil... inappropriate it may be more useful and efficient to report the fact instead of trying to fit it.

Regards
Michel

Re: Number in date column [message #337628 is a reply to message #337611] Thu, 31 July 2008 08:03 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
If the requirement st... sil... inappropriate ...



Laughing Cheers for the chortle.

[Updated on: Thu, 31 July 2008 08:03]

Report message to a moderator

Re: Number in date column [message #337637 is a reply to message #337594] Thu, 31 July 2008 08:23 Go to previous messageGo to next message
cvs_1984
Messages: 136
Registered: August 2007
Location: Punjab, INDIA
Senior Member


No way to insert number in a date column
Re: Number in date column [message #337641 is a reply to message #337637] Thu, 31 July 2008 08:33 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
cvs_1984 wrote on Thu, 31 July 2008 15:23

No way to insert number in a date column

Now there's a new insight..
Re: Number in date column [message #337642 is a reply to message #337628] Thu, 31 July 2008 08:34 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Quote:
If the requirement st... sil... inappropriate ...


Rolling Eyes
Re: Number in date column [message #337687 is a reply to message #337602] Thu, 31 July 2008 11:49 Go to previous messageGo to next message
Littlefoot
Messages: 21821
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on Thu, 31 July 2008 13:58
Is there a way to insert a date in a number column?

Yes, convert all columns to varchar2 or raw then you can insert what you want.



Actually, there's a relatively simple way to insert a DATE into a NUMBER column - a Julian date!
SQL> create table test (date_n number);

Table created.

SQL> insert into test (date_n)
  2    select to_number(to_char(sysdate, 'J'))
  3    from dual;

1 row created.

SQL> select date_n,
  2         to_char(to_date(date_n, 'J'), 'dd.mm.yyyy') date_d
  3  from test;

    DATE_N DATE_D
---------- ----------
   2454679 31.07.2008

SQL>


Michel
If the requirement st... sil... inappropriate ...
/forum/fa/449/0/
Re: Number in date column [message #337694 is a reply to message #337594] Thu, 31 July 2008 13:27 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
The only reason you would want to store a zero in a date field is to flag it for special processing. You can do this two ways. Store a special date into the column

my_date := to_date('01/01/0001','mm/dd/yyyy')

which would be findable via an index or use a null, which would not be findable via an index.


my_date := null;


a number is impossible and makes no sense.
Re: Number in date column [message #337703 is a reply to message #337694] Thu, 31 July 2008 14:18 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
...or use a third and superior way: use a flag column to flag special occurrences and use the date column for what it is meant to store.
Using 'special' values will lead to trouble, sooner or later.
Previous Topic: table update error
Next Topic: How to split the serial number
Goto Forum:
  


Current Time: Thu Nov 07 15:52:34 CST 2024