Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

C#, Oracle, datetime

Posted on 2016-08-17
11
Medium Priority
?
112 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 332 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 664 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 49

Accepted Solution

by:
PortletPaul earned 672 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
Basic Security of Your VPC

So, you’ve got this shiny new VPC and a fancy new application configured on your EC2 servers ready to go. This application is only accessible from your computer, which is great for security, but you need your users to be able to access it! So, what’s the easiest way to do this?

 
LVL 83

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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 672 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 664 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 5

Assisted Solution

by:Abhimanyu Suri
Abhimanyu Suri earned 332 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

CHALLENGE LAB: Troubleshooting Connectivity Issues

Goal: Fix the connectivity issue in the lab's AWS environment so that you can SSH into the provided EC2 instance.  

Question has a verified solution.

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

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.  …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

670 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