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
Solved

Oracle 10g Date question - Related Issue

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

856 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