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

The following query:

select cdate from rprt where cdate <= 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.

This question is related to sql oracle10g database-administration

The answer is


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

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;

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


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


try this type of format:

SELECT to_char(sysdate,'dd-mm-rrrr') FROM dual

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;

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;

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to oracle10g

Query to display all tablespaces in a database and datafiles How to insert date values into table Why do I get PLS-00302: component must be declared when it exists? ORA-28000: the account is locked error getting frequently Oracle Trigger ORA-04098: trigger is invalid and failed re-validation Inserting Image Into BLOB Oracle 10g Error System.Data.OracleClient requires Oracle client software version 8.1.7 or greater when installs setup Different CURRENT_TIMESTAMP and SYSDATE in oracle Search for a particular string in Oracle clob column ORA-00984: column not allowed here

Examples related to database-administration

How do I resolve this "ORA-01109: database not open" error? ORA-12528: TNS Listener: all appropriate instances are blocking new connections. Instance "CLRExtProc", status UNKNOWN extract date only from given timestamp in oracle sql Find out the history of SQL queries Frontend tool to manage H2 database How to replace a string in a SQL Server Table Column How can I kill all sessions connecting to my oracle database?