0

I am trying to assign a string containing the date string to a Oracle Parameter. 

txtCommence.Text = '31//05/2018' --value is from the textbox

cmd.Parameters.Add(New OracleParameter(":pCommencementDate", OracleDbType.TimeStamp)).Value = txtCommence.Text

The timestamp in the table is stored as DD-MON-RR HH.MI.SSXFF AM. How to convert the string value in the txtcommence.text to the required format in the table?

ssuhas76
  • 73
  • 10
  • 1
    Hopefully the timestamp in database **is not** stored as VARCHAR2 `DD-MON-RR HH.MI.SSXFF AM` - it should be a `TIMESTAMP` or `DATE` value. – Wernfried Domscheit Jun 06 '18 at 05:54
  • 1
    Yes, It is stored as Timestamp(6). That's the datatype of the column i am trying to the insert the value to – ssuhas76 Jun 06 '18 at 05:57
  • passing a date value would solve the issue irrespective of the format oracle is using. try using this solution https://stackoverflow.com/questions/8634568/convert-a-string-to-a-datetime to convert string to datetime and pass it to your oracle parameter – Ajay Venkata Raju Jun 06 '18 at 06:40
  • Thanks Ajay, it worked for me – ssuhas76 Jun 07 '18 at 13:22

1 Answers1

0

If possible you may change your input control from TextBox to DateTimePicker in order to get a Date value directly instead of string.

Anyway, if this is not possible you should change your SQL like this:

cmd.CommandText = " ... WHERE COMMENCEMENT_DATE = TO_TIMESTAMP(:pCommencementDate, 'dd//mm/yyyy')"
cmd.Parameters.Add(New OracleParameter(":pCommencementDate", OracleDbType.Varchar2)).Value = txtCommence.Text
Wernfried Domscheit
  • 38,841
  • 5
  • 50
  • 81