We help IT Professionals succeed at work.

Oracle Date Question or .net ADO date error

bill_home
bill_home asked
on
there is a customer who is getting this error:

ORA-1847 - day of month must be between 1 and last day of the month.

The nls format is dd-mon-rr. They are putting in dates as dd/mm/yyyy. I have watched them do this.  There are also .net date validations on the page

The error is happening in .NET. I can't look at the data before the insert as it is happening in code.

Does anyone have any suggestions as to why this is happening?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
I would change the .net app code to use a to_date on the textbox string value on insert.

You can change the database to use a new default format which I would not recommend.

All the .Net validators do is validate that it believes it is a valid date format.  Not that it is 'valid' for Oracle.

Author

Commented:
Thanks slightwv. I understand the validators. I was wondering if it was an oracle issue.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
It is a 'app' issue.

Oracle can implicitly convert a string to a date for you as long as the string has the 'default' format of dd-mon-yy or the format specified by the init (or session)parameter NLS_DATE_FORMAT.

It is bad coding to rely on implicit data conversions for the reason you are running into now.  You should always explicitly convert between data types then you can control the outcome.

If the Oracle table has a date column the .Net code should do the string to date conversion or at least let Oracle know the format using the TO_DATE SQL function.

If you can post a code snippet I can probably help with the details.

Author

Commented:
I really don't believe it is an app issue. Since these customers are the only people in North and South America with this problem.

I beleive it is some kind of strange oracle issue...I think

Thanks for your input. I will assign points accordingly.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
It could be a regional OS setting or an NLS_LANG issue but the underlying error is pretty specific:  Oracle is receiving a string it is trying to implicitly convert into a date and it does not recognize the format it is receiving.

Author

Commented:
could you expand on this NLS_LANG   -where can I take a look at that?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
still and issue , closing and assigning points

Author

Commented:
hhhh
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
If the problem isn't solved, why close the question?

Author

Commented:
still an issue closing and assigning points

Explore More ContentExplore courses, solutions, and other research materials related to this topic.