• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 994
  • Last Modified:

Oracle 10g Date question - Related Issue

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.


0
groucho47
Asked:
groucho47
  • 3
1 Solution
 
DrSQLCommented:
groucho47,
    The default format mask for a date is DD-MON-RR for you.  To see the time, extend the format.  The simplest way is with a to_char:

to_char(rptdate,'DD_MON-YYYY HH24:MI:SS')

You can also alter the format for your session:
alter session set  NLS_DATE_FORMAT  ='DD-MON-RR HH24:MI:SS';

Good luck!
0
 
groucho47Author Commented:
My question was not the original listed at the top, it was the follow up underneath it. I know all about showing the time.
0
 
Mark GeerlingsDatabase AdministratorCommented:
The insert and the query both worked because:
1. in the insert statement, an explicit format mask that matched the actual data was provided with the "to_date" operator
2. the query worked (and gave you results matching your NLS_DATE_FORMAT) because you did *NOT* include a different format mask with the "to_char" operator

Many people get confused and/or surprised by how Oracle handles and/or displays dates and times.  "Date" columns (or PL/SQL date variables) can include the time portion, but when you query a date if you do *NOT* explicitly convert it with the "to_char" operator and a format mask that includes the time portion, you will *NOT* see the time if your NLS_DATE_FORMAT does not include the time portion.

Be careful with date comparisons, like: "rpt_date = '01apr08'
or: "rpt_date = '01-apr-08'

If the actual values include the time of day (and that is not midnight) a query using this condition will *NOT* return any rows, even if there are records that include this date but have times other than midnight.

And yes, Oracle is a bit flexible in how the separators can be entered for dates.  For example, I think these all work with an NLS_DATE_FORMAT of "DD-MON-RR":
26-Jun-08
26/Jun/08
26.Jun.08

In some cases, it may even work with no separator character, but don't depend on this!
0
 
groucho47Author Commented:
Thanks. I also tried '01*apr*08' and that also worked! I wonder how many other characters oracle will now accept in place of the dash. In the case of this query, the dates the user was querying were pre-truncated (no time included) when the tables were populated. That's why I pointed out that the "Original Question" about showing the time was not mine.....I added a "related question" that was dealing with querying date columns.
0
 
groucho47Author Commented:
I just needed to find out if Oracle accepting dates in a query like '01apr08' was normal, or if something unusual was going on in the database.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now