Home » SQL & PL/SQL » SQL & PL/SQL » Timestamp column (Oracle 10g r2)
Timestamp column [message #325091] Thu, 05 June 2008 00:15 Go to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi,
I have a table with a column hire_date (Timestamp).
Previously we used to save time part also in this column .
but recently our requirements have changed and we don't have to save time part (only date part).I cannot drop the table as it has large amount of data.
How can I ignore time part and insert into this column .
i have tried Trunc function but its not working.

can anybody help on this ?

regards
Re: Timestamp column [message #325092 is a reply to message #325091] Thu, 05 June 2008 00:20 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>Trunc function but its not working
my car is not working, please make it go.
Re: Timestamp column [message #325093 is a reply to message #325091] Thu, 05 June 2008 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What "does not work" mean, it perfectly works:
SQL> select systimestamp t1, trunc(systimestamp) t2 from dual;
T1                                                                          T2
--------------------------------------------------------------------------- -------------------
05/06/2008 07:20:51.231 +02:00                                              05/06/2008 00:00:00

1 row selected.

Regards
Michel
Re: Timestamp column [message #325100 is a reply to message #325091] Thu, 05 June 2008 00:37 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Can't it be done by simply using TO_DATE ?
INSERT INTO your_tab(hire_date) VALUES(TO_DATE('12-Mar-2008','DD-Mon-YYYY'));
Re: Timestamp column [message #325102 is a reply to message #325100] Thu, 05 June 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle DATE datatype also contains a timestamp part.

Regards
Michel
Re: Timestamp column [message #325108 is a reply to message #325093] Thu, 05 June 2008 00:56 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
SELECT SYSTIMESTAMP t1, TRUNC(SYSTIMESTAMP) FROM DUAL
ORA-00932: inconsistent datatypes: expected NUMBER got TIMESTAMP

SELECT * FROM v$version

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production      
PL/SQL Release 9.2.0.1.0 - Production                           
CORE	9.2.0.1.0	Production                                       
TNS for Linux: Version 9.2.0.1.0 - Production                   
NLSRTL Version 9.2.0.1.0 - Production 
Re: Timestamp column [message #325112 is a reply to message #325108] Thu, 05 June 2008 01:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OP is working on 10gR2, so my answer.
In 9.2, you have to use a trick:
SQL> SELECT SYSTIMESTAMP t1, TRUNC(SYSTIMESTAMP+0) t2 FROM DUAL;
T1                                  T2
----------------------------------- -------------------
05-JUN-08 08.06.34.524038 AM +02:00 05/06/2008 00:00:00

1 row selected.

SQL> @v

Version Oracle : 9.2.0.4.0

Regards
Michel
Re: Timestamp column [message #325113 is a reply to message #325091] Thu, 05 June 2008 01:12 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Thanks everyone for your replies.
problem is only with Insert and not Select.
Probably due to the fact that my NLS_DATE_FORMAT has a time part and I cannot change it as lots of other tables depend on it.
Since,oracle defaults to 12:00:00 AM ,if the time is not given to it(that's what get saved when I use Trunc function),probably I have to live with this and find out some other solution (while selecting may be).


regards
Re: Timestamp column [message #325117 is a reply to message #325113] Thu, 05 June 2008 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
problem is only with Insert and not Select.

Clarify what is your problem.
Quote:
Probably due to the fact that my NLS_DATE_FORMAT has a time part

No relation with that, DATE datatype HAS a time part, ever, whatever you use as format.
What is the problem with time 00:00:00?

Regards
Michel
Re: Timestamp column [message #325129 is a reply to message #325091] Thu, 05 June 2008 01:53 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Hi ,
This is my problem;

Quote:

SQL>
SQL> Insert into test1 values (trunc(systimestamp));

1 row created.

SQL> Select * from test1;

A
---------------------------------------------------------------------------
05-JUN-08 12.00.00.000000 AM

SQL> ed
Wrote file afiedt.buf

1* Select trunc(a) from test1
SQL> /

TRUNC(A)
---------
05-JUN-08

SQL>




I don't want this '12' part in the column.
I am okay with 00:00:00

regards
Re: Timestamp column [message #325132 is a reply to message #325129] Thu, 05 June 2008 02:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Just display it in the format you want.
It is just a display issue, nothing more.
Use TO_CHAR to display your column.

Regards
Michel
Re: Timestamp column [message #325135 is a reply to message #325091] Thu, 05 June 2008 02:05 Go to previous messageGo to next message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Thanks Michel for your time Smile
Re: Timestamp column [message #325136 is a reply to message #325129] Thu, 05 June 2008 02:06 Go to previous message
mintomohan
Messages: 104
Registered: November 2006
Location: India
Senior Member
If you need 00:00:00, you have to use the 24-hour time format and not the 12-hour one.

Regards
Minto
Previous Topic: how to write this sql query
Next Topic: How to calculate the Bytes of multibyte character.
Goto Forum:
  


Current Time: Sun Dec 04 23:12:17 CST 2016

Total time taken to generate the page: 0.04306 seconds