I need a Time column and a Date column in SQL Server

[TexasAggie]

I want to insert time into one column and date into another column. SQL is giving me hell trying to do this because my formats or data types my not be meshing between SQL and VB.

 

This is what I've tried: (i have tried other methods as well)

 

           If Not sqlCmd.ExecuteScalar Then
               sqlCmd.CommandText = "CREATE TABLE " & TableName & " (" & _
               "[RowCount] BIGINT NOT NULL PRIMARY KEY, " & _
               "Count INT NOT NULL, " & _
               "RunNumber INT NOT NULL, " & _
               "CurrentCycle INT NOT NULL, " & _
               "CurrentMode INT NOT NULL, " & _
               "TimeInMode INT NOT NULL, " & _
               "CurrentTime VARCHAR(8) NOT NULL," & _
               "CurrentDate DATE NOT NULL," & _
               "[Version] VARCHAR(10) NOT NULL);"
               sqlCmd.ExecuteNonQuery()

               Columns = "[RowCount],Count,RunNumber, CurrentCycle,CurrentMode,TimeInMode,CurrentTime, CurrentDate, [Version]"
               Data = "0,0,0,0,0,0,['" & Format(Now.Hour, "00") & ":" & Format(Now.Minute, "00") & ":" & Format(Now.Second, "00") & "']," & DateTimePicker1.Value.Date & ",['0.0.0']"

               cmdString = "INSERT INTO " & TableName & "(" & Columns & ") VALUES ( " & Data & ")"
               sqlCmd = New SqlCommand(cmdString, sqlConn)
               sqlCmd.ExecuteNonQuery()
           End If

[PlausiblyDamp]

A couple of quick things to start, as you are using .Net rather than the Format command it might be easier to use .ToString() (or an appropriate overload) to get the date formatted as a string.

 

You could get the same string from the date by using Date.Now.ToString("HH:mm:ss").

 

One other thing you might want to try is avoiding building SQL strings by using string concatenation - it can lead to errors due to data type mismatches as well as making the code itself harder to read and debug. http://www.xtremedotnettalk.com/showthread.php?p=463520#post463520 might be worth a read to give you a pointer on the topic.

[TexasAggie]

I would prefer to use a real data format that SQL will recognize so that I can perform date functions later on.

[PlausiblyDamp]

If you use a parameterised SQL statement then you can just pass a data in directly and not need to convert to a string.

[TexasAggie]

What exact date object should I used when inserting a line of data into SQL such that SQL will understand it for ORDER BY, etc.? Edited May 7, 2012 by TexasAggie

[PlausiblyDamp]

If the underlying column is specified as being a date then SQL will treat it as a date for sorting and filtering automatically.

[TexasAggie]

This is more of what I was looking for:

 

Time: Now.TimeOfDay.ToString

Date: DateTime.Today

 

Thanks, however!