Thanks for the question, Mahesh.
Asked: October 30, 2015 - 10:41 am UTC
Last updated: October 30, 2015 - 5:29 pm UTC
Version: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit
Viewed 1000+ times
You Asked
Hi,
I am trying to insert data into a date field using a timestamp field using the query mentioned below
insert into NBA_REP.NANBA_IMPRESSION_TEMP
(
IMPRESSION_DATE ,
--------
-------
)
select
impression_timestamp as IMPRESSION_DATE ,
--------
-------
After inserting the data when i ran the query
01-APR-15 18
01-APR-15 8
01-APR-15 18
01-APR-15 12
01-APR-15 12
01-APR-15 8
02-APR-15 4
02-APR-15 36
02-APR-15 2
02-APR-15 12
02-APR-15 18
and also when i ran another query
SELECT * FROM NANBA.NANBA_IMPRESSION_TEMP
where impression_date like '01-apr-2015';
it returned no rows.
I was expecting only one entry for apr 1st in the group by query and i was expecting records for second query ask well.
Could you please advise me in this?
Thanks,
Bodhi
and Chris said...
There's a couple of problems with this:
1. Oracle is case sensitive. Your output shows the months in uppercase. You're comparing to a month in lowercase. These don't match, so you're query won't return anything.
2. Assuming that IMPRESSION_DATE is actually a date (and not a string pretending to be a date) then you've got implicit conversions going on. So you're relying on client nls settings for this query to work:
create table t (
x date
);
insert into t values (sysdate);
alter session set nls_date_format = 'dd-MON-yyyy';
select * from t;
X
-----------
30-OCT-2015
select * from t
where x like '30-oct-2015';
no rows selected
alter session set nls_date_format = 'dd-mon-yyyy';
select * from t;
X
-----------
30-oct-2015
select * from t
where x like '30-oct-2015';
X
-----------
30-oct-2015