select data based on a date column
being_uncertain
I was trying to select some data from my table using the following query:
select * from table1 where column1 = to_date('14-05-14','yy-mm-dd');
Where the column data type is DATE. I observed that, the above query won't return anything unless we modified it as,
select * from table1 where trunc(column1) = to_date('14-05-14','yy-mm-dd');
even though there are records available.
I checked the documentation for TRUNC.Can anyone please explain why this happens?
UPDATE
As per the valuable comments I think some time values may also associated with the DATE. But I cannot view/edit that time. How can I ensure there are time values associated.
Dba
Both TO_DATE and TRUNC are different. See the below example.
SQL> ALTER SESSION SET nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
Session altered.
SQL> SELECT TO_DATE(SYSDATE) FROM DUAL;
TO_DATE(SYSDATE)
-------------------
28/05/2014 16:03:25
SQL> SELECT TRUNC(SYSDATE) FROM DUAL;
TRUNC(SYSDATE)
-------------------
28/05/2014 00:00:00
In Your first query to_date('14-05-14','yy-mm-dd') is comparing with the date field column1 in your table which has time values also. Whereas in Your 2nd query You are truncating the time part from table data and from Your query, that's why it's matching.
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
TOP Ranking
- 1
pump.io port in URL
- 2
How to import an asset in swift using Bundle.main.path() in a react-native native module
- 3
Failed to listen on localhost:8000 (reason: Cannot assign requested address)
- 4
Double spacing in rmarkdown pdf
- 5
SQL Server : need add a dot before two last character
- 6
C++ 16 bit grayscale gradient image from 2D array
- 7
JMeter: Why get error when try to save test plan
- 8
JWT gives JsonWebTokenError "invalid token"
- 9
How to make thrown errors visible outside of a Promise?
- 10
How to tell if iOS Today Widget is being updated in the background?
- 11
Calling Doctrine clear() with an argument is deprecated
- 12
Capybara Selenium Chrome opens About Google Chrome
- 13
How to update azerothcore-wotlk docker container
- 14
Adding Ripple Effect to RecyclerView item
- 15
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)
- 16
Error while applying filter on dataframe - PySpark
- 17
Unable to add slack to bluemix project
- 18
MyPy fails dataclass argument with optional list of objects type
- 19
How can I validate and parse phone numbers to extract their country calling code and area code?
- 20
Single Sign-On in Spring by using SAML Extension and Shibboleth
- 21
python how to create many-to-many of lists inside one list
Comments