Solved

C#, Oracle, datetime

Posted on 2016-08-17
11
74 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
[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
  • 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 35

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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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 35

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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, 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 recover a database from a user managed backup

756 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