CODEWITHSUNDEEP
c#sqloracle-databaseto-date
tinkerbell
tinkerbell

Reputation: 421

TO_DATE() function gives an error when date format variable is 'M/d/yy' and month or date is a two digit value

My date format is varying according to the date format of the user. When the date format is 'M/d/yy', it gives error, ORA-01821: date format not recognized. 

sDateFormat = CultureInfo.DateTimeFormat.ShortDatePattern;

SELECT * FROM xxx WHERE yyy <= TO_DATE('" + sScheduleDate + "','"+sDateFormat+"')";

//sScheduleDate ex: "11/15/18" //sDateFormat "M/d/yy"

Upvotes: 0

Views: 1071

Answers (2)

kara
kara

Reputation: 3455

I see two problems:

  1. Like HoTTab1CH said: You should always use OracleParameters if building queries (https://en.wikipedia.org/wiki/SQL_injection)

  2. You're using a C#-Pattern for Oracle. This might work (Invariant Culture) bot often don't (Minutes).

.

// This will get you the Pattern "MM/dd/yyyy"
string invariantPattern = CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern;

// This will get me in a German-Environment the Pattern "dd.MM.yyyy"
string invariantPattern = CultureInfo.InvariantCulture.DateTimeFormat.ShortDatePattern;

Both Patterns have nothing todo with Oracle. They might work, but you don't know. You must not use them outside of your .Net environment!

What would be allowed is this:

OracleCommand cmd = null; // You should have this one already initialized..

// Your Idea:
DateTime date = new DateTime(2018, 12, 31, 23, 59, 59);
string csharpPattern = "dd.MM.yyyy HH:mm:ss";
string oraclePattern = "dd.mm.yyyy HH24:MI:SS";
string toDateQuery = "to_date('" + date.ToString(csharpPattern) + "','" + oraclePattern + "')";
string sqlQuery = "SELECT * FROM mytable t WHERE t.mydate = " + toDateQuery;
cmd.CommandText = sqlQuery;
var reader = cmd.ExecuteReader();
// Do something...

// But... Better, shorter and correcter(?)
DateTime date2 = new DateTime(2018, 12, 31, 23, 59, 59);
cmd.CommandText = "SELECT * FROM mytable t WHERE t.mydate = :MYDATE";
cmd.Parameters.Add(new OracleParameter(":MYDATE", date2));

Additional explaination

Table

CREATE TABLE TEST
(
  TESTDATE               DATE,
  TESTTIMESTAMP          TIMESTAMP(6),
  TESTTIMESTAMPTIMEZONE  TIMESTAMP(6) WITH TIME ZONE
)

C#-App

DateTime d = DateTime.Now;

// Let OPD.Net do the work..
OracleCommand  cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO TEST VALUES(:TESTDATE, :TESTTIMESTAMP, :TESTTIMESTAMPTIMEZONE)";
cmd.Parameters.Add(new OracleParameter("TESTDATE", d));
cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMP", d));
cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMPTIMEZONE", d));
cmd.ExecuteNonQuery();

// Try to manually hit the OracleTypes - and loose the milliseconds..
cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO TEST VALUES(:TESTDATE, :TESTTIMESTAMP, :TESTTIMESTAMPTIMEZONE)";
cmd.Parameters.Add(new OracleParameter("TESTDATE", OracleDbType.Date, d, System.Data.ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMP", OracleDbType.Date, d, System.Data.ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMPTIMEZONE", OracleDbType.Date, d, System.Data.ParameterDirection.Input));
cmd.ExecuteNonQuery();

// Set everything correct (and redundant..)
cmd = con.CreateCommand();
cmd.CommandText = "INSERT INTO TEST VALUES(:TESTDATE, :TESTTIMESTAMP, :TESTTIMESTAMPTIMEZONE)";
cmd.Parameters.Add(new OracleParameter("TESTDATE", OracleDbType.Date, d, System.Data.ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMP", OracleDbType.TimeStamp, d, System.Data.ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter("TESTTIMESTAMPTIMEZONE", OracleDbType.TimeStampTZ, d, System.Data.ParameterDirection.Input));
cmd.ExecuteNonQuery();

Db-Data

| TESTDATE            | TESTTIMESTAMP              | TESTTIMESTAMPTIMEZONE             |
| 16/08/2018 11:07:23 | 16/08/2018 11:07:23,079714 | 16/08/2018 11:07:23,079714 +02:00 |
| 16/08/2018 11:07:23 | 16/08/2018 11:07:23,000000 | 16/08/2018 11:07:23,000000 +02:00 | 
| 16/08/2018 11:07:23 | 16/08/2018 11:07:23,079714 | 16/08/2018 11:07:23,079714 +02:00 |

As you can see. The example-program did choose the wrong types. Without explicit types in the cmd, ODP.Net does it's job correct.

OPD.Net has a mapping for every C#-Type to OracleDbTypes. You don't have to tell Oracle what a DateTime is!

https://docs.oracle.com/cd/B28359_01/win.111/b28375/featTypes.htm

If you start setting types in your C#-code you've got a double-declaration. Your Database tells your client how to convert variables.

If you change the Db-Column from Date to Timestamp you would have to change your C#-App, too! If you got multiple Apps accessing your Db you got a lot of work.

There are some situations, like using arrays with nullable types, where you should set the type, but regularly you don't have to.

Upvotes: 1

HoTTab1CH
HoTTab1CH

Reputation: 199

It's not recognized because there is no such "M" parameter, here is full documentation for TO_DATE() https://www.techonthenet.com/oracle/functions/to_date.php

Try this, correct formatting mask should be to_date('11/15/18', 'MM/DD/YY')

EDIT:

To avoid this problem you better use parameters in your query. Here is small example:

 DateTime date = //get your date here

 string myQuery= "SELECT * FROM xxx WHERE yyy <= :pDate";

 OracleCommand oraCmd = new OracleCommand();
 oraCmd.CommandType = CommandType.Text;
 oraCmd.Connection = OracleConnectionSource; // your connection

 OracleParameter oraParam = new OracleParameter();
 oraParam = oraCmd.Parameters.Add("pDate", OracleDbType.Date, date, ParameterDirection.Input);

 oraCmd.CommandText = myQuery;
 OracleDataReader oraDataReader = oraCmd.ExecuteReader();

Upvotes: 0

Related Questions