Original Question:
I am inserting this sql statement into an Oracle 10g database:
INSERT INTO ActivityLog(ID, date) values(41, to_date ('02-27-2007 09:17:55 PM', 'MM-DD-YYYY HH:MI:SS AM'))
It appears to work fine, but when I query it, only the date is showing?? Please explain what is going wrong. Thanks
My related question is as follows:
If I query NLS_DATABASE_PARAMETERS, I get the following from our 10.2.0.3 database
PARAMETER VALUE
--------------------------
---- --------------------------
--
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.3.0
We have a user that submitted a query with dates in the where clause, as follows:
SELECT DISTINCT (rpt_date)
FROM mtry_tran_hist
WHERE rpt_date >= '01apr08' AND rpt_date < '01may08'
and the query got results. I tried this myself as system with no alteration of my session parameters, and it worked.
Did I miss something? How can that a date formatted in a way that does NOT match the NLS_DATE_FORMAT get results that are correct? Did Oracle ALWAYS allow us to leave out the dashes, and I just did not know it?. I also did the same query using the "official" date format, and of course I got the same results.
Start Free Trial
