Home » SQL & PL/SQL » SQL & PL/SQL » sql developer seprate date and time (oracle 11g express edition + oracle sql developer)
icon5.gif  sql developer seprate date and time [message #569807] Thu, 01 November 2012 18:59 Go to next message
tartar
Messages: 3
Registered: November 2012
Junior Member

Hello,
I have a question with Oracle sql developer, i've installed oracle 11g express edition,
and i want to insert values 'date' and 'time', but i only got the resultat like this:
./fa/10483/0/
Who can tell me how can i do to show just date info in 'date' and same for the 'time'?
thanks a lot.
Re: sql developer seprate date and time [message #569808 is a reply to message #569807] Thu, 01 November 2012 19:02 Go to previous messageGo to next message
tartar
Messages: 3
Registered: November 2012
Junior Member

ps: and i also tried to_char('10/12/2010','DD/MM/YYYY'),to_char('15:00:00','HH24:MI:SS'), but it didn't work at all.
Re: sql developer seprate date and time [message #569809 is a reply to message #569808] Thu, 01 November 2012 19:17 Go to previous messageGo to next message
BlackSwan
Messages: 23186
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

DATE datatype contains both "date" & "time" components; See below

SQL> SELECT SYSDATE DATE_AND_TIME, TO_CHAR(SYSDATE,'YYYY-MM-DD') DATE_ONLY, 
TO_CHAR(SYSDATE,'HH24:MI:SS') TIME_ONLY FROM DUAL;

DATE_AND_TIME       DATE_ONLY  TIME_ONL
------------------- ---------- --------
2012-11-01 16:59:18 2012-11-01 16:59:18


[Updated on: Fri, 02 November 2012 02:03] by Moderator

Report message to a moderator

Re: sql developer seprate date and time [message #569819 is a reply to message #569807] Fri, 02 November 2012 01:01 Go to previous messageGo to next message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
tartar wrote on Fri, 02 November 2012 00:59
how can i do to show just date info in 'date' and same for the 'time'?

As you were told, DATE datatype contains both date and time.

But, as you want to show them separately, TO_CHAR might solve the problem:
SQL> select sysdate,
  2         to_char(sysdate, 'dd.mm.yyyy') date_only,
  3         to_char(sysdate, 'hh24:mi:ss') time_only
  4  from dual;

SYSDATE             DATE_ONLY  TIME_ONL
------------------- ---------- --------
02.11.2012 07:01:11 02.11.2012 07:01:11

SQL>
Re: sql developer seprate date and time [message #569860 is a reply to message #569819] Fri, 02 November 2012 09:35 Go to previous messageGo to next message
tartar
Messages: 3
Registered: November 2012
Junior Member

thanks a lot for the responses.
yes, i'm aware of that 'DATE datatype contains both date and time', but it's the prof who insists showing date and time seprately in the table,
and i've seen your solutions before, but for me, it's a query, not to 'insert values' in the table.
so i'd like to know how can i be able to have a table directly presente date and time.
thanks again.
Re: sql developer seprate date and time [message #569861 is a reply to message #569860] Fri, 02 November 2012 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A table does not present anything.
A table contains data.
YOU, with your application, SQL*Plus for instance, presents the data as you want.
And for this, you use TO_CHAR.

Regards
Michel
Re: sql developer seprate date and time [message #569876 is a reply to message #569808] Fri, 02 November 2012 12:38 Go to previous messageGo to next message
joy_division
Messages: 4559
Registered: February 2005
Location: East Coast USA
Senior Member
tartar wrote on Thu, 01 November 2012 20:02
ps: and i also tried to_char('10/12/2010','DD/MM/YYYY'),to_char('15:00:00','HH24:MI:SS'), but it didn't work at all.


TO_CHAR on a character string is pointless and as you see in your case, does not work.
Re: sql developer seprate date and time [message #569887 is a reply to message #569860] Fri, 02 November 2012 14:51 Go to previous message
Littlefoot
Messages: 19901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
tartar wrote on Fri, 02 November 2012 15:35
it's the prof who insists showing date and time seprately in the table


Perhaps you should tell him/her that that can't be done - not smart way, that is. You could, of course, create a VARCHAR2 datatype columns and put result of TO_CHAR function (one for date, another for time) into each of them. But that's really useless because - what can you do with those values (except looking at them thinking how pretty they are)?
Previous Topic: Query to find the user who did not login from past 3 years
Next Topic: Query without outer join
Goto Forum:
  


Current Time: Fri Dec 26 15:29:00 CST 2014

Total time taken to generate the page: 0.20510 seconds