Solved

Oracle 10g Date question - Related Issue

Posted on 2008-06-26
5
989 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

752 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