How to insert current date and time in sql server using wincc

Question

I am Saving data in SQL server using WINCC but I can't insert my current data and time Help me to solve this

Sub OnClick(ByVal Item)                                      
Dim objConnection
Dim strConnectionString
Dim lngValue
Dim strSQL
Dim objCommand
strConnectionString = "Provider=MSDASQL;DSN=winnrc;UID=;PWD=;"
lngValue = HMIRuntime.Tags("JOB_ID").Read(1)
strSQL = "INSERT INTO NEWDATA (JOB_ID,SHIFT,QTY,WEIGHT,DECREASING_ST,DECREASING_TEMP_PV,DECREASING_LEVEL,CASCADE_RINSE_T4_ST,CASCADE_RINSE_T4_TEMP_PV,CASCADE_RINSE_T4_LEVEL,CASCADE_RINSE_T3_ST,CASCADE_RINSE_T3_TEMP_PV,CASCADE_RINSE_T3_LEVEL,PHOSPHATE_ST,PHOSPHATE_TEMP_PV,PHOSPHATE_LEVEL,trigger_0) VALUES (" & HMIRuntime.Tags("REPORT_BLOCK_JOB_ID").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_SHIFT").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_QUANTITY").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_WEIGHT").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_DECREASING_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_DECREASING_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_DECREASING_LEVEL").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T4_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T4_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T4_LEVEL").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T3_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T3_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T3_LEVEL").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_PHOSPHATE_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_PHOSPHATE_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_PHOSPHATE_LEVEL").Read(1) & "," & HMIRuntime.Tags("trigger_0").Read(1) & ");"
Set objConnection = CreateObject("ADODB.Connection")
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject("ADODB.Command")
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing
End Sub

Answer 1

Please follow these steps :

1) If you have not added a column for date and time in your table NEWDATA please add a new column name (dt - for instance) and its datatype as datetime

2)Then in the vbscript add the following lines

dim dtat
dtat = now

And rewrite the insert statement as given below :

strSQL = "INSERT INTO NEWDATA(JOB_ID,SHIFT,QTY,WEIGHT,DECREASING_ST,DECREASING_TEMP_PV,DECREASING_LEVEL,CASCADE_RINSE_T4_ST,CASCADE_RINSE_T4_TEMP_PV,CASCADE_RINSE_T4_LEVEL,CASCADE_RINSE_T3_ST,CASCADE_RINSE_T3_TEMP_PV,CASCADE_RINSE_T3_LEVEL,PHOSPHATE_ST,PHOSPHATE_TEMP_PV,PHOSPHATE_LEVEL,trigger_0,dt) VALUES (" & HMIRuntime.Tags("REPORT_BLOCK_JOB_ID").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_SHIFT").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_QUANTITY").Read(1) & "," & MIRuntime.Tags("REPORT_BLOCK_WEIGHT").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_DECREASING_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_DECREASING_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_DECREASING_LEVEL").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T4_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T4_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T4_LEVEL").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T3_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T3_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_CASCADE_RINSE_T3_LEVEL").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_PHOSPHATE_ST").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_PHOSPHATE_TEMP_PV").Read(1) & "," & HMIRuntime.Tags("REPORT_BLOCK_PHOSPHATE_LEVEL").Read(1) & "," & HMIRuntime.Tags("trigger_0").Read(1) & ",convert(datetime,'"&dtat&"',103));"

Hope that helps!