Oracle "invalid format" when working with Date

Question

I've used SQL Server for years but I'm new to Oracle SQL. I'm trying to refactor an old application and one of the things I'm doing is moving it to the newer Oracle Managed Data Access. The application uses all in-line SQL with parameters.

I'm having an issue with the a query that deals with dates. I know Oracle expects dates to be in a specific format but I thought if I used the TO_DATE function I could work with whatever format I use.

When I try to run the following query with parameters I'm getting an error

One of the identified items was in an invalid format

I'm assuming it's with the date piece.

If I use LinqPad and run the following query it will update just fine.

update SAP_DATES 
set DATE_VALUE = TO_DATE('2010-03-26','yyyy-mm-dd'), 
    DATE_MODIFIED = TO_DATE('2015-06-12','yyyy-mm-dd')  
where 
    EMP = '00000197' and DATE_TYPE = 'Retirement Date     '

However, if I use the following I get the following I get the error. I can confirm the date values right before calling the SQL being in a 'yyyy-mm-dd' format. No hours or minutes.

 public const String UPDATE_DATE_RECORD_SQL = "update SAP_DATES set " +
            "DATE_VALUE = TO_DATE(:dtValue,'yyyy-mm-dd'), DATE_MODIFIED = TO_DATE(:dtModified,'yyyy-mm-dd') " + 
            "where EMP = :Emp and DATE_TYPE = :dtType";

and in the data layer

using (objConnection)
{
    OracleCommand objCmd = objConnection.CreateCommand();
    objCmd.CommandText = oracleCmdText;

    objCmd.Parameters.Add(":Emp", OracleDbType.Varchar2).Value = employeeId;
    objCmd.Parameters.Add(":dtType", OracleDbType.Varchar2).Value = dtType;
    objCmd.Parameters.Add(":dtValue", OracleDbType.Date).Value = dtValue;
    objCmd.Parameters.Add(":dtModified", OracleDbType.Date).Value = dtModified;

    objConnection.Open();
    objCmd.ExecuteNonQuery();
}

Anyone know why I can run the query using LinqPad but if I use the in-line SQL with parameters I get the error?

:dtValue and :dtModified should be of type Varchar2 because TO_DATE is expecting a string in the specified format for conversion into a date. — Glenn
– Glenn, Commented Apr 11, 2016 at 18:17
Perfect! That was the issue. Post this as an answer and I can give you the credit for it. Thanks! — Caverman
– Caverman, Commented Apr 11, 2016 at 18:26

Glenn · Accepted Answer · 2016-04-11 22:34:01Z

1

:dtValue and :dtModified should be of type Varchar2 because TO_DATE is expecting a string in the specified format for conversion into a date.-

answered Apr 11, 2016 at 22:34

Glenn

9,2102 gold badges44 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cableload · Accepted Answer · 2016-04-11 18:08:12Z

1

The order in which you add the parameters should be in the same order in which you have defined the update query in your sql statement

So use this order

     objCmd.Parameters.Add(":dtValue", OracleDbType.Date).Value = dtValue;
     objCmd.Parameters.Add(":dtModified", OracleDbType.Date).Value = dtModified;
     objCmd.Parameters.Add(":Emp", OracleDbType.Varchar2).Value = employeeId;
     objCmd.Parameters.Add(":dtType", OracleDbType.Varchar2).Value = dtType;

answered Apr 11, 2016 at 18:08

cableload

4,4156 gold badges42 silver badges65 bronze badges

1 Comment

Caverman Over a year ago

Thanks for the suggestion but unfortunately that didn't seem to make a difference. It was my understanding that when you alias the parameter with a name it shouldn't matter the order but then again I'm new to Oracle so I could be wrong.

Paul Roub · Accepted Answer · 2017-04-19 19:11:40Z

1

I have that problem and in my case, it was not caused by the Date type, but caused by the oracle database binding attributes by position, and you need to bind by name...

oracleCommand.BindByName = true

edited Apr 19, 2017 at 19:11

Paul Roub

36.5k27 gold badges88 silver badges95 bronze badges

answered Apr 19, 2017 at 18:02

Rafael Andrs Cspedes Basterio

4,4311 gold badge19 silver badges12 bronze badges

1 Comment

Andrew S Over a year ago

This applies if working with the Oracle.ManagedDataAccess.Client namespace. A headache for my recent conversion from .NET 3 to 4.7.

Collectives™ on Stack Overflow

Oracle "invalid format" when working with Date

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related