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

T

trend

Well-known member
Joined
Oct 12, 2004
Messages
171
I have this code:

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
 
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 .Nets native sql support.
 
trend said:
Code: 
 Dim myInsertQuery As String = "INSERT INTO DateTimePayload (Date) Values(date1)"
Click to expand...
Your string will be like
Code: 
INSERT INTO DateTimePayload (Date) Values(date1)
try instead
Code: 
 Dim myInsertQuery As String = "INSERT INTO DateTimePayload (Date) Values(" & date1 & ")"
and you will get
Code: 
INSERT INTO DateTimePayload (Date) Values(2005-10-03)

hth
/Kejpa
 
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.
 
PlausiblyDamp said:
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.
Click to expand...

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

Code: 
Dim Parm3 As SqlParameter = .SelectCommand.Parameters.Add("@movedate", SqlDbType.DateTime)
Code: 
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 said:
Code: 
Parm3.Value = movedate
If you are using parameters you dont need to worry about the formatting.
Click to expand...

else it will come out with a overflow problem,

I do it by this
Code: 
Parm3.Value = movedate.Year.ToString() & "-" & movedate.Month.ToString() & "-" & movedate.Day.ToString()
is that any easier way to do it
 
What do you mean by
else it will come out with a overflow problem
Click to expand...

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

You shouldnt need to be formatting the date as a string when using parameters - that is one of the main reasons for using parameters.
 
PlausiblyDamp said:
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 shouldnt need to be formatting the date as a string when using parameters - that is one of the main reasons for using parameters.
Click to expand...

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.
 
Back
Top