extract date only from given timestamp in oracle sql

Question

The following query   select cdate from rprt where cdate  lt   TO CHAR sysdate   YYYY MM DD-HH24-MI-SS-SSSSS   and ryg  R  and cnum  C002     return  2013 04 27-10 06 26 794 as stored in the table   I want to get the date only as   27-04-2013 and get the number of days between the resul tdate and sysdate

User · Answer

Convert Timestamp to Date as mentioned below  it will work for sure -   select TO DATE TO CHAR TO TIMESTAMP   2015-04-15 18 00 22 000    YYYY-MM-DD HH24 MI SS FF    MM DD YYYY HH24 MI SS    MM DD YYYY HH24 MI SS   dt from dual

User · Answer

This format worked for me  for the mentioned date format i e  MM DD YYYY  SELECT to char query date  MM DD YYYY   as query date  FROM QMS INVOICE TABLE

User · Answer

Use the function cast   to convert from timestamp to date   select to char cast sysdate as date   DD-MM-YYYY   from dual    For more info of function cast oracle11g http   docs oracle com cd B28359 01 server 111 b28286 functions016 htm SQLRF51256

User · Answer

If you want the value from your timestamp column to come back as a date datatype  use something like this   select trunc my timestamp column  dd   as my date column from my table

User · Answer

This is exactly what TO DATE   is for  to convert timestamp to date   Just use TO DATE sysdate  instead of TO CHAR sysdate   YYYY MM DD-HH24-MI-SS-SSSSS     SQLFiddle demo  UPDATE   Per your update  your cdate column is not real DATE or TIMESTAMP type  but VARCHAR2  It is not very good idea to use string types to keep dates  It is very inconvenient and slow to search  compare and do all other kinds of math on dates   You should convert your cdate VARCHAR2 field into real TIMESTAMP  Assuming there are no other users for this field except for your code  you can convert cdate to timestamp as follows   BEGIN TRANSACTION  -- add new temp field tdate  ALTER TABLE mytable ADD tdate TIMESTAMP  -- save cdate to tdate while converting it  UPDATE mytable SET tdate   to date cdate   YYYY-MM-DD HH24 MI SS     -- you may want to check contents of tdate before next step     -- drop old field ALTER TABLE mytable DROP COLUMN cdate  -- rename tdate to cdate  ALTER TABLE mytable RENAME COLUMN tdate TO cdate  COMMIT    SQLFiddle Demo

User · Answer

try this type of format   SELECT to char sysdate  dd-mm-rrrr   FROM dual

User · Answer

In Oracle 11g  To get the complete date from the Timestamp  use this- Select TRUNC timestamp  FROM TABLE NAME   To get the Year from the Timestamp  use this- Select EXTRACT YEAR FROM TRUNC timestamp   from TABLE NAME   To get the Month from the Timestamp  use this- Select EXTRACT MONTH FROM TRUNC timestamp   from TABLE NAME   To get the Day from the Timestamp  use this- Select EXTRACT DAY FROM TRUNC timestamp   from TABLE NAME

[sql] extract date only from given timestamp in oracle sql

Examples related to sql

Examples related to oracle10g

Examples related to database-administration