OraFAQ Forum: SQL & PL/SQL » sql developer seprate date and time

Home » SQL & PL/SQL » SQL & PL/SQL » sql developer seprate date and time (oracle 11g express edition + oracle sql developer)

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

sql developer seprate date and time [message #569807]

Thu, 01 November 2012 18:59

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:
/forum/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.

Attachment: sql date time.JPG
(Size: 34.69KB, Downloaded 3943 times)

Report message to a moderator

Re: sql developer seprate date and time [message #569808 is a reply to message #569807]

Thu, 01 November 2012 19:02

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.

Report message to a moderator

Re: sql developer seprate date and time [message #569809 is a reply to message #569808]

Thu, 01 November 2012 19:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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

Littlefoot
Messages: 21825
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>

Report message to a moderator

Re: sql developer seprate date and time [message #569860 is a reply to message #569819]

Fri, 02 November 2012 09:35

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.

Report message to a moderator

Re: sql developer seprate date and time [message #569861 is a reply to message #569860]

Fri, 02 November 2012 10:06

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: sql developer seprate date and time [message #569876 is a reply to message #569808]

Fri, 02 November 2012 12:38

joy_division
Messages: 4963
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.

Report message to a moderator

Re: sql developer seprate date and time [message #569887 is a reply to message #569860]

Fri, 02 November 2012 14:51

Littlefoot
Messages: 21825
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)?

Report message to a moderator

Previous Topic:	Query to find the user who did not login from past 3 years
Next Topic:	Query without outer join

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Aug 13 19:14:21 CDT 2025