Solved

C#, Oracle, datetime

Posted on 2016-08-17
11
47 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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
 
LVL 78

Expert Comment

by:David Johnson, CD, MVP
Comment Utility
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
Comment Utility
Here is a solution.
Change your query to below
SELECT * FROM XXX.XXXXXXX WHERE TRUNC(changed ) > '10-AUG-16'
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 19

Expert Comment

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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

744 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

13 Experts available now in Live!

Get 1:1 Help Now