Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.8K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 468 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
how to get a time part from Date datatype

715267
Member Posts: 9
Hi,
I would like to know how to extract the timestamp alone from DATE datatype? If my input is '27-SEP-2011 23:59:00' I need the output as 23:59:00. I am surprised to see that there are no in-built functions for this or may be I am wrong. Basically I need to remove the date part from DATE data type and get the time.Please assist.
-Thanks
Edited by: user9546145 on Sep 27, 2011 2:24 PM
Edited by: user9546145 on Sep 27, 2011 2:25 PM
I would like to know how to extract the timestamp alone from DATE datatype? If my input is '27-SEP-2011 23:59:00' I need the output as 23:59:00. I am surprised to see that there are no in-built functions for this or may be I am wrong. Basically I need to remove the date part from DATE data type and get the time.Please assist.
-Thanks
Edited by: user9546145 on Sep 27, 2011 2:24 PM
Edited by: user9546145 on Sep 27, 2011 2:25 PM
Answers
-
Hi,user9546145 wrote:Be careful! In Oracle, TIMESTAMP means a datatype, similar to but distinct from DATE. You'll avoid confusion if you don't use the word "timestamp" to mean anything else.
Hi,
I would like to know how to extract the timestamp alone from DATE datatype?
There is a built-in function, TO_CHAR:TO_CHAR (dt_col, 'HH24:MI:SS')
Depending on how you plan to use the time, TRUNC is another handy built-in function:dt_col - TRUNC (dt_col)
is a NUMBER (not less than 0, but less than 1) which is suitable for many tasks, such as finding the average time.
I hope this answers your question.
If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables, and also post the results you want from that data.
Explain, using specific examples, how you get those results from that data.
Always say which version of Oracle you're using. -
TO_CHAR is your builtin function.
See http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements004.htm#i34510SQL> -- generating sample date: SQL> with t as ( 2 select to_date('27-SEP-2011 23:59:00', 'dd-mon-yyyy hh24:mi:ss') dt from dual 3 ) 4 -- 5 -- actual query: 6 -- 7 select to_char(dt, 'hh24:mi:ss') 8 from t; TO_CHAR( -------- 23:59:00 1 row selected.
-
HI,
You may use to_date and to_char function together, it your parameter is text:
E.g: if you passing '27-SEP-2011 23:59:00' as a varchar2 datatype then first convert it into date and then extract the time from it; for that use this command:
SQL> select to_char(to_date('27-SEP-2011 23:59:00','dd-mm-yyyy HH24:MI:SS'),'HH24:MI:SS AM') time from dual;
TIME
--------
23:59:00
if you already passing the date type as parameter then just use to_char function for extract the time from it.
E.g:
Select to_char(sysdate,'HH24:MI:SS AM') from dual;
the AM will indicates that either its morning time or afternoon time automatically.
I hope this is your answer
Regards
M.A.Bamboat -
Hi,
Thanks all for your inputs..!!:)
This discussion has been closed.