locked
Get current date and time RRS feed

  • Question

  • I had following coding:

    Dim date1 as date
    time1 = DateValue(Format(Now(), "yyyy/MM/dd ")) + TimeValue(Format(Now(), "HH:mm"))
    

    How to make it suitable for datatype DATE in oracle?

    Thank you.


    • Edited by brownie yeo Thursday, September 29, 2011 5:21 AM
    Thursday, September 29, 2011 5:13 AM

Answers

  • Ok. I am using Oracle 11g.

    Thank you for your post answer.


    I had get my ans.

    oledbCMD = New OleDbCommand("INSERT INTO ots_output (User_ID,Name,Equip,Lot,Task,Readout,Temp,Qty,Timestart,Remark,Cstate) VALUES ('" & User_ID & "','" & cboxUser.Text & "','" & cboxEquip.Text & "','" & txtLot.Text & "','" & cboxTask.Text & "','" & cboxReadout.Text & "','" & cboxTemp.Text & "','" & txtQty.Text & "','"  & CStr(Format(Now(), "yyyy-MM-dd HH:mm")) & "','" & txtRemark1.Text & "', 'Move In')", myConn)
    oledbCMD.ExecuteScalar()


    • Edited by brownie yeo Friday, September 30, 2011 1:13 AM
    • Marked as answer by Liliane Teng Wednesday, October 5, 2011 8:23 AM
    Friday, September 30, 2011 12:15 AM

All replies

  • Don't use strings in Oracle but use the dateTimeObject and than parameters.

    I've no documentation of the latest Oracle .Net class at hand but probably something like

    OraclCommand.Parameters.AddWithValue("time1",Now)

    The DateTime object in Oracle is likewise .Net however, like for SQL Server will the tick format (which keeps the data as a ticks after a certain starting date time) be different and converted by the add parameter process.

     

     


    Success
    Cor
    • Edited by Cor Ligthert Thursday, September 29, 2011 5:30 AM starting date time
    Thursday, September 29, 2011 5:29 AM
  • Thank you for your reply.

    But i dont understand ur post..

    Can you show some exp coding?

    Thank you.

    Thursday, September 29, 2011 5:41 AM
  • I can but not without to know what you do with that date and time.

    In which way are you adding this to SQL Transact and in which way are you using that SQL Tranact (DataReader,executeQuery, ExecuteScalar or DataAdapter)


    Success
    Cor
    • Edited by Cor Ligthert Thursday, September 29, 2011 5:48 AM
    Thursday, September 29, 2011 5:47 AM
    • Looks like VB6 code. If it's VB.Net, you should enable Option Strict.
    • You can not add two Dates. DateValue returns a Date and TimeValue also. What is 12/17/2009 + 3/24/2011? Not possible. The line could be written much shorter:
      date1 = Now()
    • Instead of formatting values yourself, use Parameters with the OracleCommand object

    Armin
    Thursday, September 29, 2011 5:52 AM
  • I can but not without to know what you do with that date and time.

    In which way are you adding this to SQL Transact and in which way are you using that SQL Tranact (DataReader,executeQuery, ExecuteScalar or DataAdapter)


    Success
    Cor

    I want to save the current time into oracle database when the user click button SAVE.

    But the user does not need to choose the date and time.

    Is the system direct capture and store it.

    Thursday, September 29, 2011 6:41 AM
    • Looks like VB6 code. If it's VB.Net, you should enable Option Strict.
    • You can not add two Dates. DateValue returns a Date and TimeValue also. What is 12/17/2009 + 3/24/2011? Not possible. The line could be written much shorter:
      date1 = Now()
    • Instead of formatting values yourself, use Parameters with the OracleCommand object

    Armin

    then how can i put it into oracle database with column TIMESTART and datatype is DATE.

    I think the current date1 is string.

    Thursday, September 29, 2011 6:41 AM
  • Do you use VB code for that or do you only have the button?

    If you have the VB code than show that (not more than 5 rows is needed)


    Success
    Cor
    Thursday, September 29, 2011 6:44 AM
  • I don't know which skills you already have in database programming. Do I have to start at the beginning (how to connect to a database etc) or do you have a specific question?

    What does this mean: "I think the current date1 is string." A Date is not a String. It's a DateTime object. An alias name for the type in VB is Date.


    Armin
    Thursday, September 29, 2011 6:45 AM
  • I had the coding for my SAVE button.

     

    oledbCMD = New OleDbCommand("INSERT INTO ots_output (User_ID,Name,Equip,Lot,Task,Readout,Temp,Qty,Timestart,Remark,Cstate) VALUES ('" & User_ID & "','" & cboxUser.Text & "','" & cboxEquip.Text & "','" & txtLot.Text & "','" & cboxTask.Text & "','" & cboxReadout.Text & "','" & cboxTemp.Text & "','" & txtQty.Text & "','" & time1 & "','" & txtRemark1.Text & "', 'Move In')", myConn)
    oledbCMD.ExecuteScalar()

    the time 1 i highlight is the time i want to put inside oracle database.

     

    Thursday, September 29, 2011 6:51 AM
  • I am using visual basic.

    but i can connect database already. The problem now is the i cant store the date1 into my oracle database.

    my oracle database has a column TIMESTART with datatype is DATE want to store date1.

    Thursday, September 29, 2011 6:54 AM
  • Have a look here: http://msdn.microsoft.com/en-us/library/ms254953(VS.90).aspx

    It explains how to use a command object to modify data. There's also a chapter about how to use parameters.

    I still don't know why date1 must be a String.

     


    Armin
    Thursday, September 29, 2011 7:05 AM
  • I just took two fields, you have to do the rest according to this. 

    Be aware the parameter names are not really names you have to add the parameters in the order as in the SQL string.

            oledbCMD = New OleDbCommand("INSERT INTO ots_output (User_ID,TheDate) VALUES (@ID,@DateTo)", myConn)
            OleDBCommand.Parameters.AddWithValue("@ID", User_ID)
            OleDBCommand.Parameters.AddWithValue("@DateTo", Now)
            oledbCMD.ExecuteNonQuery()
    


    Also be aware that I changed your executeScalar for an ExecuteNonQuery. The ExecuteScalar is meant to select one value from the database.

    (Everything typed without checking for typos and absolute not tested)

     


    Success
    Cor
    • Proposed as answer by Armin Zingler Thursday, September 29, 2011 7:29 AM
    Thursday, September 29, 2011 7:09 AM
  • Sorry. I dont need set date 1 as string. Actually i just want to save the current date and time into my oracle database which had set DATE as datatype. And the datatype is confusing me.

    Thursday, September 29, 2011 7:10 AM
  • Look at the sample code I gave you in my reply to you.

     


    Success
    Cor
    Thursday, September 29, 2011 7:11 AM
  • I just took two fields, you have to do the rest according to this. 

    Be aware the parameter names are not really names you have to add the parameters in the order as in the SQL string.

     

            oledbCMD = New OleDbCommand("INSERT INTO ots_output (User_ID,TheDate) VALUES (@ID,@DateTo)", myConn)
            OleDBCommand.Parameters.AddWithValue("@ID", User_ID)
            OleDBCommand.Parameters.AddWithValue("@DateTo", Now)
            oledbCMD.ExecuteNonQuery()
    

     


    Also be aware that I changed your executeScalar for an ExecuteNonQuery. The ExecuteScalar is meant to select one value from the database.

    (Everything typed without checking for typos and absolute not tested)

     


    Success
    Cor


    Thank you for your reply. After i put it inside.

    Looks like i need to do many correction for it.

    My current coding cant make correction?

    Thursday, September 29, 2011 7:35 AM
  • Yea that can be but that is not a part of your question.

    It has probably to do with the connectionstring (I'm not sure if OleDB is the right choice, which version of Oracle you are using which version framework you using, framework 1.x had inbuild a provider for Oracle).

    However, if you are visiting this forums, and your question is in fact answered then mark all the answers which have helped you as answer and vote all replies which where helpful as helpful. 

    In that way other persons searching for an answer can benefit from your problem. If follow up questions are answered questions thread becomes mostly thrash because of the length and sometimes a complete different reply is marked as answer because the original question is forgot by the OP (original poster).

     


    Success
    Cor
    Thursday, September 29, 2011 12:52 PM
  • Ok. I am using Oracle 11g.

    Thank you for your post answer.


    I had get my ans.

    oledbCMD = New OleDbCommand("INSERT INTO ots_output (User_ID,Name,Equip,Lot,Task,Readout,Temp,Qty,Timestart,Remark,Cstate) VALUES ('" & User_ID & "','" & cboxUser.Text & "','" & cboxEquip.Text & "','" & txtLot.Text & "','" & cboxTask.Text & "','" & cboxReadout.Text & "','" & cboxTemp.Text & "','" & txtQty.Text & "','"  & CStr(Format(Now(), "yyyy-MM-dd HH:mm")) & "','" & txtRemark1.Text & "', 'Move In')", myConn)
    oledbCMD.ExecuteScalar()


    • Edited by brownie yeo Friday, September 30, 2011 1:13 AM
    • Marked as answer by Liliane Teng Wednesday, October 5, 2011 8:23 AM
    Friday, September 30, 2011 12:15 AM