Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 134
  • Last Modified:

C#, Oracle, datetime

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
NJJoeG8GT
Asked:
NJJoeG8GT
  • 3
  • 2
  • 2
  • +3
6 Solutions
 
Russ SuterCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
PortletPaulCommented:
"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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
David Johnson, CD, MVPOwnerCommented:
AFAIK don't use MON for month use mm for 2 digit month MMM for 3 Character Month.
0
 
Manoj PatilSr. Software EngineerCommented:
Here is a solution.
Change your query to below
SELECT * FROM XXX.XXXXXXX WHERE TRUNC(changed ) > '10-AUG-16'
0
 
Manoj PatilSr. Software EngineerCommented:
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
 
PortletPaulCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Abhimanyu SuriCommented:
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
 
Manoj PatilSr. Software EngineerCommented:
Hey Guys, Thanks
Your comments are very informative and helpful.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now