Solved

Oracle 10g Date question - Related Issue

Posted on 2008-06-26
5
988 Views
Last Modified: 2013-12-18
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
Comment
Question by:groucho47
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 22

Expert Comment

by:DrSQL
ID: 21875431
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
 

Author Comment

by:groucho47
ID: 21875469
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
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 100 total points
ID: 21875470
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
 

Author Comment

by:groucho47
ID: 21875719
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
 

Author Closing Comment

by:groucho47
ID: 31470994
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
pivot rows to columns 1 44
oracle forms question 22 48
What are the limitations of input parameters in oracle ? 5 63
Oracle programming for starter 14 27
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question