0

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();
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Von Justine Napalang
  • 67
  • 9
  • `date` datatype in Oracle always contains both date and time components. If you apply `trunc` to it, that value will be "rounded" to midnight that day (e.g. 26.07.2019 07:13:22 will become 26.07.2019 00:00:00), but time (00:00:00) will remain. Therefore, see whether applying `to_char` function with appropriate format mask (recognizable in the target) will help; for example, `to_char(date_value, 'dd.mm.yyyy')` will result in 26.07.2019 (there won't be 00:00:00 any more). – Littlefoot Jul 26 '19 at 05:15
  • i mean this ,string date = Request.Form[txtDate.UniqueID]; how can iconvert this, sorry im new in oracle and asp.net – Von Justine Napalang Jul 26 '19 at 05:23
  • and why is the data in table when i use toad its only 7/26/2019 – Von Justine Napalang Jul 26 '19 at 05:26
  • I know nothing about asp.net. As of TOAD: date format you see depends on how you set that property in TOAD **Options**. – Littlefoot Jul 26 '19 at 05:48

1 Answers1

0

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

Syafiqur__
  • 531
  • 7
  • 15