[sql] Convert timestamp to date in Oracle SQL

How can we convert timestamp to date?

The table has a field, start_ts which is of the timestamp format:

'05/13/2016 4:58:11.123456 PM'

I need to query the table and find the maximum and min timestamp in the table but I'm not able to.

Select max(start_ts) 
from db 
where cast(start_ts as date) = '13-may-2016'

But the query is not returning any values.

Please help me in finding the max timestamp for a date.

This question is related to sql oracle11g

The answer is

CAST(timestamp_expression AS DATE)

For example, The query is : SELECT CAST(SYSTIMESTAMP AS DATE) FROM dual;

Try using TRUNC and TO_DATE instead

    TRUNC(start_ts) = TO_DATE('2016-05-13', 'YYYY-MM-DD')

Alternatively, you can use >= and < instead to avoid use of function in the start_ts column:

   start_ts >= TO_DATE('2016-05-13', 'YYYY-MM-DD')
   AND start_ts < TO_DATE('2016-05-14', 'YYYY-MM-DD')

Format like this while selecting:

to_char(systimestamp, 'DD-MON-YYYY')


select to_char(systimestamp, 'DD-MON-YYYY') from dual;

If the datatype is timestamp then the visible format is irrelevant.

You should avoid converting the data to date or use of to_char. Instead compare the timestamp data to timestamp values using TO_TIMESTAMP()

WHERE start_ts >= TO_TIMESTAMP('2016-05-13', 'YYYY-MM-DD')
   AND start_ts < TO_TIMESTAMP('2016-05-14', 'YYYY-MM-DD')

You can try the simple one

select to_date('2020-07-08T15:30:42Z','yyyy-mm-dd"T"hh24:mi:ss"Z"') from dual;

You can use:

select to_date(to_char(date_field,'dd/mm/yyyy')) from table

If you have milliseconds in the date string, you can use the following.

select TO_TIMESTAMP(SUBSTR('2020-09-10T09:37:28.378-07:00',1,23), 'YYYY-MM-DD"T"HH24:MI:SS:FF3')From Dual;

And then convert it to Date with:

select trunc(TO_TIMESTAMP(SUBSTR('2020-09-10T09:37:28.378-07:00',1,23), 'YYYY-MM-DD"T"HH24:MI:SS:FF3')) From Dual;

It worked for me, hope it will help you as well.

I'd go with the following:

Select max(start_ts) 
from db 
where trunc(start_ts) =  date'13-may-2016'

This may not be the correct way to do it. But I have solved the problem using substring function.

Select max(start_ts), min(start_ts)from db where SUBSTR(start_ts, 0,9) ='13-may-2016'

using this I was able to retrieve the max and min timestamp.

Questions with sql tag:

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 Export result set on Dbeaver to CSV How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server IF EXISTS THEN 1 ELSE 2 How to add a boolean datatype column to an existing table in sql? Presto SQL - Converting a date string to date format What is the meaning of <> in mysql query? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Convert timestamp to date in Oracle SQL #1292 - Incorrect date value: '0000-00-00' Postgresql tables exists, but getting "relation does not exist" when querying SQL query to check if a name begins and ends with a vowel Find the number of employees in each department - SQL Oracle Error in MySQL when setting default value for DATE or DATETIME Drop view if exists Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers TypeError: tuple indices must be integers, not str Select Rows with id having even number SELECT list is not in GROUP BY clause and contains nonaggregated column IN vs ANY operator in PostgreSQL How to insert date values into table Error related to only_full_group_by when executing a query in MySql How to select the first row of each group? Connecting to Microsoft SQL server using Python eloquent laravel: How to get a row count from a ->get() How to execute raw queries with Laravel 5.1? In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function Rebuild all indexes in a Database SQL: Two select statements in one query DB2 SQL error sqlcode=-104 sqlstate=42601 What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types How to run .sql file in Oracle SQL developer tool to import database? Concatenate columns in Apache Spark DataFrame How Stuff and 'For Xml Path' work in SQL Server? Fatal error: Call to a member function query() on null

Questions with oracle11g tag:

Convert timestamp to date in Oracle SQL Query to display all tablespaces in a database and datafiles Oracle Installer:[INS-13001] Environment does not meet minimum requirements Forgot Oracle username and password, how to retrieve? Extract number from string with Oracle function How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 Add days Oracle SQL How to determine tables size in Oracle ORA-28000: the account is locked error getting frequently Oracle listener not running and won't start How to calculate difference between two dates in oracle 11g SQL PLS-00428: an INTO clause is expected in this SELECT statement Getting error in console : Failed to load resource: net::ERR_CONNECTION_RESET The listener supports no services NLS_NUMERIC_CHARACTERS setting for decimal copy from one database to another using oracle sql developer - connection failed Oracle SqlDeveloper JDK path Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection? BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed Default Values to Stored Procedure in Oracle How to call Oracle MD5 hash function? How to find the users list in oracle 11g db? How to find schema name in Oracle ? when you are connected in sql session using read only user Nth max salary in Oracle How to echo text during SQL script execution in SQLPLUS PL/SQL ORA-01422: exact fetch returns more than requested number of rows Could not load file or assembly "Oracle.DataAccess" or one of its dependencies How to create a new schema/new user in Oracle Database 11g? How to move table from one tablespace to another in oracle 11g The network adapter could not establish the connection - Oracle 11g Oracle SQL query for Date format How to update cursor limit for ORA-01000: maximum open cursors exceed SQL Error: ORA-00942 table or view does not exist Grant SELECT on multiple tables oracle how to determine size of tablespace oracle 11g ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client Difference between number and integer datatype in oracle dictionary views Is it possible to use "return" in stored procedure? IO Error: The Network Adapter could not establish the connection Error while trying to retrieve text for error ORA-01019 How to get the last row of an Oracle a table Display names of all constraints for a table in Oracle SQL java.sql.SQLException: Missing IN or OUT parameter at index:: 1 Oracle pl-sql escape character (for a " ' ") MAX(DATE) - SQL ORACLE SQL to add column and comment in table in single command Using SQL LOADER in Oracle to import CSV file ORA-12514 TNS:listener does not currently know of service requested in connect descriptor How can I select from list of values in Oracle Timestamp conversion in Oracle for YYYY-MM-DD HH:MM:SS format