Solved

Oracle 10g Date question - Related Issue

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

758 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now