How can you store date/time in MSSQL in datetime format?

[trend]

I have this code:

 

       Dim connString As String = "Provider=SQLOLEDB.1;User Id=sa;Password=password;Initial Catalog=Northwind;server=localhost;Use Encryption for Data=False"
       Dim myconnection As OleDbConnection = New OleDbConnection(connString)

       ' If the connection string is null, use a default.
       Dim date1 As String = Date.Today.ToString("yy-MM-dd")
       Try
           Dim myInsertQuery As String = "INSERT INTO DateTimePayload (Date) Values(date1)"
           Dim myCommand As New OleDbCommand(myInsertQuery)
           myCommand.Connection = myconnection
           myconnection.Open()
           myCommand.ExecuteNonQuery()
           myCommand.Connection.Close()
       Catch
           MsgBox(Err.Description)
       End Try

 

That is all I have so far... I basically just want to store the date in one field, and time in another (time down to the minutes is fine)...

 

How can I do this? with my above code, I get an error about date1 not being able to be there or something..

 

ideas?

 

thanks

Lee

[PlausiblyDamp]

You cannot just use .Net variable inside an SQL command like that, you would be much better off looking at using either a parameterised query or a stored procedure that accepts parameters; search these forums for examples of both ways - they crop up a lot in questions here.

Also if you are connection to a SQL server you might want to use the classes under the SqlClient namespace rather than OLEDB as then you will not be going through the OleDb layer but will be using .Net's native sql support.

[kejpa]

 Dim myInsertQuery As String = "INSERT INTO DateTimePayload (Date) Values(date1)"

Your string will be like

INSERT INTO DateTimePayload (Date) Values(date1)

try instead

 Dim myInsertQuery As String = "INSERT INTO DateTimePayload (Date) Values('" & date1 & "')"

and you will get

INSERT INTO DateTimePayload (Date) Values('2005-10-03')

 

hth

/Kejpa

[trend]

Heh, nice work around!

[PlausiblyDamp]

Trust me you will encounter problems sooner or later due to the formatting of the date using this method - a stored procedure or a parameterised query will be much less error prone.

[miwaypro]

Trust me you will encounter problems sooner or later due to the formatting of the date using this method - a stored procedure or a parameterised query will be much less error prone.

 

I have the following and i want to format the date and time

 

Dim Parm3 As SqlParameter = .SelectCommand.Parameters.Add("@movedate", SqlDbType.DateTime)

Parm3.Value = movedate.GetDateTimeFormats(("dd-mm-yyyy hh:mm:ss"))

 

that is something wrong with my second line of code, can u pls help me

[PlausiblyDamp]

Parm3.Value = movedate

If you are using parameters you don't need to worry about the formatting.

[miwaypro]

Parm3.Value = movedate

If you are using parameters you don't need to worry about the formatting.

 

else it will come out with a overflow problem,

 

I do it by this

Parm3.Value = movedate.Year.ToString() & "-" & movedate.Month.ToString() & "-" & movedate.Day.ToString()

is that any easier way to do it

[PlausiblyDamp]

What do you mean by

else it will come out with a overflow problem

 

Are you getting an overflow when you use the code I posted? How is the underlying table column defined (datetime, smalldatetime etc)?

 

You shouldn't need to be formatting the date as a string when using parameters - that is one of the main reasons for using parameters.

[miwaypro]

What do you mean by

 

Are you getting an overflow when you use the code I posted? How is the underlying table column defined (datetime, smalldatetime etc)?

 

You shouldn't need to be formatting the date as a string when using parameters - that is one of the main reasons for using parameters.

 

yes, that is an error message pop-up when i use your without format it. The column is defined as DateTime. The problem is solve after i use the toString, i believe that is some way that is better than toString that i can use, hope u can help me.