Converting datetime to date in asp.net using oracle

Question

I have this date from my jQuery datetime picker, I able to retrieve what the user selects, but in Oracle the datatype of Datehired is Date.

The question is how am I going to convert this to date only in order it to insert in my DB?

This is date from jQuery:

 string dt = Request.Form[txtDate.UniqueID];

This is my jQuery:

<asp:TextBox ID="txtDate" runat="server" ReadOnly = "true"></asp:TextBox>
<script type="text/javascript">
$(function () {
    $("[id*=txtDate]").datepicker();
});
</script>

My insert statement:

string query = "sp_employee_insert";

if (con.State == ConnectionState.Open)
{
    con.Close();
}

con.Open();

OracleCommand cmd = new OracleCommand(query, con);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@in_EMPLOYEEID", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_EMPLOYEENAME", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_STATUS", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_SUPERIORID", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_MANAGERSUPERIORID", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_BFG", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_DATEHIRED", OracleDbType.Date);
cmd.Parameters.Add("@in_DESIGNATION", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_JOBGRADE", OracleDbType.Int32);
cmd.Parameters.Add("@in_EMAILADDRESS", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_PRODUCTLINE", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_STATION", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_MACHINE", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_OPERATIONGROUP", OracleDbType.Varchar2);
cmd.Parameters.Add("@in_ISACTIVE", OracleDbType.Varchar2);

cmd.Parameters["@in_EMPLOYEEID"].Value = txtEMPLOYEEID.Text;
cmd.Parameters["@in_EMPLOYEENAME"].Value = txtEMPLOYEENAME.Text;
cmd.Parameters["@in_STATUS"].Value = txtSTATUS.Text;
cmd.Parameters["@in_SUPERIORID"].Value = txtSUPERIORID.Text;
cmd.Parameters["@in_MANAGERSUPERIORID"].Value = txtMANAGERSUPERIORID.Text;
cmd.Parameters["@in_BFG"].Value = txtBFG.Text;
cmd.Parameters["@in_DATEHIRED"].Value = date;
cmd.Parameters["@in_DESIGNATION"].Value = txtDESIGNATION.Text;
cmd.Parameters["@in_JOBGRADE"].Value = txtJOBGRADE.Text;
cmd.Parameters["@in_EMAILADDRESS"].Value = txtEMAILADDRESS.Text;
cmd.Parameters["@in_PRODUCTLINE"].Value = txtPRODUCTLINE.Text;
cmd.Parameters["@in_STATION"].Value = txtSTATION.Text;
cmd.Parameters["@in_MACHINE"].Value = txtMACHINE.Text;
cmd.Parameters["@in_OPERATIONGROUP"].Value = txtOPERATIONGROUP.Text;
cmd.Parameters["@in_ISACTIVE"].Value = txtISACTIVE.Text;

cmd.ExecuteNonQuery();

Answer 1

Oracle has to functions that are amaizing to work with dates. to_char and to_date.

Say I have DAteTime Is following: 8/13/2009 12:00:00 AM

so on your SQL statement just use to_date function. this function accepts 2 arguments. first is the given date and second is the format.

EX :

to_date('" + DTtoOracle.Date.ToString("yyyy-MM-dd") + "','yyyy-mm-dd')

so what we are doing is specify the format on the first argument from your datetime variable and on the second argument you are telling oracle whats the format you are sending to the database.

Full Ex :

SELECT * FROM MYTABLE WHERE DATEFIELD = to_date('" + DTtoOracle.Date.ToString("yyyy-MM-dd") + "','yyyy-mm-dd');

this should return all records where your date field = same date as your variable. Hope this help. Cheers