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

C#, Oracle, datetime

Posted on 2016-08-17
11
69 Views
1 Endorsement
Last Modified: 2016-09-26
i have been having a hard time retrieving rows from Oracle in my C# program.
the are two rows with CHANGED date greater than AUGUST 10th.  if i could itget working in TOAD i probably get the code working.


column definition TIMESTAMP(6).
the data in the column '14-AUG-16 08.51.51.043000 AM'.

in the program:

the TODATE is not working for me as i cant get the format with the literal as month.
i cant get a two year formatter. with a month that is literal(AUG).


in TOAD:

SELECT * FROM XXX.XXX WHERE changed > TO_DATE('10-AUG-16 1:00:00 AM','DD-MON-YYYY HH:MI:SS AM');

error:
ORA-01843: not a valid month.

any help would greatly appreciated.




Details:
SELECT * FROM XXX.XXXXXXX WHERE changed > TO_DATE('10-AUG-16 1:00:00 AM','MON-DD-YYYY HH:MI:SS AM')
                                                                *
Error at line 1
ORA-01843: not a valid month
1
Comment
Question by:NJJoeG8GT
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 20

Assisted Solution

by:Russ Suter
Russ Suter earned 83 total points
ID: 41760303
Your two examples have different date formats specified. The first is 'DD-MON-YYYY HH:MI:SS AM' whereas the second is 'MON-DD-YYYY HH:MI:SS AM'

The second case is trying to format the date as having the month name before the day part which is inconsistent with the date format you provided. In your first case the date and date format match properly.

In the 2nd case it's trying to match 10 with MON which certainly won't work.
0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 166 total points
ID: 41760321
Also, in both cases you are specifying 2 digit year in the string, but a 4 digit year in the format mask.

And, you are using TO_DATE.  You should be using TO_TIMESTAMP.  No sense converting it twice, make the conversion once.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 168 total points
ID: 41760336
"the data in the column "

with any date or timestamp don't confuse what you "see" with what is actually stored because they are utterly different.

what you "see" is a default format mask
the actual data is numeric

You can use your own preferred format in the to_date() or to_timestamp() functions

SELECT *
FROM XXX.XXXXXXX
WHERE changed >  to_timestamp('2016-08-16 13:00:00','yyyy-mm-dd hh24:mi:ss')

use your preferred format
0
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.

 
LVL 80

Expert Comment

by:David Johnson, CD, MVP
ID: 41760406
AFAIK don't use MON for month use mm for 2 digit month MMM for 3 Character Month.
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 41760603
Here is a solution.
Change your query to below
SELECT * FROM XXX.XXXXXXX WHERE TRUNC(changed ) > '10-AUG-16'
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 41760606
For C#
You might be taking a date from DateTime Picker
So use below code

DateTime FromDate = Convert.ToDateTime(txtFromDate.Text);
string FDate = FromDate.ToString("dd-MMM-yyyy");

Open in new window


and pass the FDate in the query

SELECT * FROM XXX.XXXXXXX WHERE TRUNC(changed ) > '"+FDate+"';
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 168 total points
ID: 41760622
For good performance do NOT add a function on the data....

Truncating the data using TRUNC() is NOT NEEDED and it removes the ability to use an index* so it can slow down your query a great deal.

I also think that it will return a date (not a timestamp) so while these are compatible data types there is an implied data conversion happening as well.

*Unless you create a function based index, but that seems like overkill for this.

{+edit}
I suggest reading this brief but useful article

Both DATE and TIMESTAMP types *always* contain a date and time component. At exactly midnight the time is 00:00:00.
Never rely on implicit conversions of strings to dates, or dates to strings. Always explicitly perform the conversions with the TO_CHAR, TO_DATE and TO_TIMESTAMP functions, ...
TRUNC() does returns a date with time set to midnight, but it isn't a timestamp. Do please be careful with the data types.
1
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 166 total points
ID: 41760808
Never do this:

SELECT * FROM XXX.XXXXXXX WHERE TRUNC(changed ) > '10-AUG-16'

There are 2 things wrong with that.  The indexing issue already talked about, and the fact that you are relying on the format mask in the environment to be set to 'dd-mon-yy'.  This is so easily changed by the user it isn't funny, every client can have a different default.

In production code, you should never ever rely in implicit conversions.

Also, given that no time component was given in the date, then the TRUNC is totally  unnecessary anyway.
1
 
LVL 4

Assisted Solution

by:Abhimanyu Suri
Abhimanyu Suri earned 83 total points
ID: 41761111
Please check NLS settings, for example

SELECT REQUEST_TIMESTAMP
  FROM REQUEST
 WHERE REQUEST_TIMESTAMP >
          TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-YY HH:MI:SS AM')

OR,

SELECT REQUEST_TIMESTAMP
  FROM REQUEST
 WHERE REQUEST_TIMESTAMP >
          TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-RRRR HH:MI:SS AM')
         
OR,

SELECT REQUEST_TIMESTAMP
  FROM REQUEST
 WHERE REQUEST_TIMESTAMP >
          TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-RR HH:MI:SS AM')  
         
             
8/12/2016 3:52:31.793000 PM
8/12/2016 3:52:54.416000 PM
8/12/2016 3:53:19.550000 PM

SELECT REQUEST_TIMESTAMP
  FROM REQUEST
 WHERE REQUEST_TIMESTAMP >
          TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM')

 -- Wrong data because
SELECT TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-YYYY HH:MI:SS AM')
  FROM DUAL
  8/10/0016 1:00:00 AM
But , NO Error stating wrong month
         
         

select  TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-RR HH:MI:SS AM')   from dual

8/10/2016 1:00:00 AM

select systimestamp - TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-RR HH:MI:SS AM')   from dual  -- Doesn't fail either

+08 09:51:08.130779

Above tests concludes that , it is not an error with the date formatting.
Also, I totally agree with the experts to not use function.

But, check this

ALTER SESSION SET NLS_DATE_LANGUAGE='FRENCH';   <------

SELECT REQUEST_TIMESTAMP
  FROM GLOBALCUSTOMER_ADMIN.USER_LOGOFF_REQUEST_TBL
 WHERE REQUEST_TIMESTAMP >
          TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-YY HH:MI:SS AM');


Session altered.
                   *
Error at line 4
ORA-01843: not a valid month


Session altered.

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';  <------


SELECT REQUEST_TIMESTAMP
  FROM GLOBALCUSTOMER_ADMIN.USER_LOGOFF_REQUEST_TBL
 WHERE REQUEST_TIMESTAMP >
          TO_DATE ('10-AUG-16 1:00:00 AM', 'DD-MON-YY HH:MI:SS AM');

REQUEST_TIMESTAMP                                
--------------------------------------------------
8/12/2016 3:52:31.793000 PM                      
8/12/2016 3:52:54.416000 PM                      
8/12/2016 3:53:19.550000 PM                      

3 rows selected.

Hence, the suggestion please check NLS settings.
0
 
LVL 19

Expert Comment

by:Manoj Patil
ID: 41761434
Hey Guys, Thanks
Your comments are very informative and helpful.
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.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to take different types of Oracle backups using RMAN.

790 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