How to add current time to a table

[a1a4a]

Hello all
I am on a windows application form where people can log in and add status ... and it's all online via sql database.
What i am looking for is a sql query that Insert into the table the current time.
Not the user time because he might be different from another user ...

Thanks for reading :D

[WareveR]

Hello all
I am on a windows application form where people can log in and add status ... and it's all online via sql database.
What i am looking for is a sql query that Insert into the table the current time.
Not the user time because he might be different from another user ...

Thanks for reading :D

Hi, i have looked for the same not to long ago due to architectural changes in my project witch is also a Windows Form Application in Visual Studio 2010 and now with MySQL 5.1 Database.

There are several ways to do what you want, but First?

Are you using MySQL?

If so, check the next link: MySQL 5.1 DateTime

There are Timestamp and Current Date or Time functions that will do that. This will save the date/time in the database computer.

If you are using a different architecture or having trouble doing that just reply.

[WareveR]

Just an example:

I'm coding in VB, but the MySQL String is the same, just change the Table_Name and Field_Name for yours.

Example:
I use these next functions and more in a side class inside the project for me to use as needed (just call the function in code)

In order to be able to use MySQL functions you need to install the MySQL Connector . Depends on the programming language you're using.

You will need this in your class.

Imports MySql.Data.MySqlClient

This code i use to create and return a database connection on the fly, very useful.

Public Shared Function get_DB_Connection() As MySql.Data.MySqlClient.MySqlConnection
    Dim conn_Local As New MySql.Data.MySqlClient.MySqlConnection( _
        "server=localhost;" & _
        "uid=myUsername;" & _
        "pwd=myPassword;" & _
        "database=myDatabase;" & _
        "protocol=socket;" & _
        "port=3306;")
    Return conn_Local
End Function

This function is used for the SQL commands INSERT, DELETE and UPDATE, it receives a String with the SQL command and returns the result, for this example i created the String locally.
The String "local_myInsertQuery" representing the SQL Command has three columns in the table for a Date value, a Time value and a DateTime value, and you can get the database Date and Time variables Using the SQL functions "CURDATE()", "CURTIME()", "NOW()", like in the example below.
(it´s the same code i use but did not test the SQL String Command)

Public Shared Function SQL_Ins_Del_Upd(ByVal myInsertQuery As String) As Integer
    Dim local_myInsertQuery As String = "INSERT INTO Table_Name (" & _
                                  "Field_Name_Date, " & _
                                  "Field_Name_Time, " & _
                                  "Field_Name_DateTime" & _
                                  ") VALUES (" & _
                                  "CURDATE(), " & _
                                  "CURTIME(), " & _
                                  "NOW()" & _
                                  ")"

    Dim result As Integer = 0

    Dim myCommand As New MySqlCommand()
    myCommand.CommandText = local_myInsertQuery
    myCommand.Connection = get_DB_Connection()
   
    Try
        myCommand.Connection.Open()
        Try
            result = myCommand.ExecuteNonQuery()
        Catch myError As MySqlException
            MsgBox("[ERROR]: SQL Querry." & vbNewLine & _
                   myError.Message & vbNewLine & _
                   myInsertQuery, _
                   MsgBoxStyle.Critical)
        End Try
    Catch myError As MySqlException
        MsgBox("[ERROR] SQL Connection: " & vbNewLine & _
               myError.Message, _
               MsgBoxStyle.Critical)
    Finally
        If myCommand.Connection.State <> ConnectionState.Closed Then
            myCommand.Connection.Close()
        End If
    End Try

    Return result

End Function

Any questions just reply.

[a1a4a]

Just an example:

I'm coding in VB, but the MySQL String is the same, just change the Table_Name and Field_Name for yours.

Example:
I use these next functions and more in a side class inside the project for me to use as needed (just call the function in code)

In order to be able to use MySQL functions you need to install the MySQL Connector . Depends on the programming language you're using.

You will need this in your class.

Imports MySql.Data.MySqlClient

This code i use to create and return a database connection on the fly, very useful.

Public Shared Function get_DB_Connection() As MySql.Data.MySqlClient.MySqlConnection
    Dim conn_Local As New MySql.Data.MySqlClient.MySqlConnection( _
        "server=localhost;" & _
        "uid=myUsername;" & _
        "pwd=myPassword;" & _
        "database=myDatabase;" & _
        "protocol=socket;" & _
        "port=3306;")
    Return conn_Local
End Function

This function is used for the SQL commands INSERT, DELETE and UPDATE, it receives a String with the SQL command and returns the result, for this example i created the String locally.
The String "local_myInsertQuery" representing the SQL Command has three columns in the table for a Date value, a Time value and a DateTime value, and you can get the database Date and Time variables Using the SQL functions "CURDATE()", "CURTIME()", "NOW()", like in the example below.
(it´s the same code i use but did not test the SQL String Command)

Public Shared Function SQL_Ins_Del_Upd(ByVal myInsertQuery As String) As Integer
    Dim local_myInsertQuery As String = "INSERT INTO Table_Name (" & _
                                  "Field_Name_Date, " & _
                                  "Field_Name_Time, " & _
                                  "Field_Name_DateTime" & _
                                  ") VALUES (" & _
                                  "CURDATE(), " & _
                                  "CURTIME(), " & _
                                  "NOW()" & _
                                  ")"

    Dim result As Integer = 0

    Dim myCommand As New MySqlCommand()
    myCommand.CommandText = local_myInsertQuery
    myCommand.Connection = get_DB_Connection()
   
    Try
        myCommand.Connection.Open()
        Try
            result = myCommand.ExecuteNonQuery()
        Catch myError As MySqlException
            MsgBox("[ERROR]: SQL Querry." & vbNewLine & _
                   myError.Message & vbNewLine & _
                   myInsertQuery, _
                   MsgBoxStyle.Critical)
        End Try
    Catch myError As MySqlException
        MsgBox("[ERROR] SQL Connection: " & vbNewLine & _
               myError.Message, _
               MsgBoxStyle.Critical)
    Finally
        If myCommand.Connection.State <> ConnectionState.Closed Then
            myCommand.Connection.Close()
        End If
    End Try

    Return result

End Function

Any questions just reply.

Thanks :D Your code helped me to pick up my answer

[WareveR]

Thanks :D Your code helped me to pick up my answer

Glad to be helpful.

Don't forget to set the thread as "SOLVED".

Regards

[AndreRet]

@a1a4a, please mark this thread as solved if you have managed to get a solution, found at the bottom of this page, thanks.:)

It has been open for some time now.