asked on

adding date into the datetime column from the asp.net page

Hello Experts

I have a column of datetime type and i am trying to insert record using the following the statement

SqlQuery.Append("INSERT INTO LearnItResults (CustomerID, TestID, GiftCodeID, StartTime) VALUES(")
SqlQuery.Append(CustomerID & ",")
SqlQuery.Append(TestID & ",")
SqlQuery.Append("'" & GiftCodeID & "',")
SqlQuery.Append("'" & System.DateTime.UtcNow & "'")
SqlQuery.Append(")")

however i am getting "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

The statement has been terminated." when i add MM/DD/YYYY format.

Please can someone advise how can i add YYYY/MM/DD format from the script?

thanks in advance
regards
kay

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

oops... is yyyy/mm/dd the format it is using?

Instead of values(), use select:

and

SqlQuery.Append("convert(datetime, '" & System.DateTime.UtcNow & "',111)")

that will accept yyyy/mm/dd as the date format.

SqlQuery.Append("INSERT INTO LearnItResults (CustomerID, TestID, GiftCodeID, StartTime) select")
                SqlQuery.Append(CustomerID & ",")
                SqlQuery.Append(TestID & ",")
                SqlQuery.Append("'" & GiftCodeID & "',")
                SqlQuery.Append("convert(datetime, '" & System.DateTime.UtcNow & "',111)")
                SqlQuery.Append("")

Open in new window

SOLUTION

devsolns

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BrandonGalderisi

But it is still ALWAYS a good idea to not depend upon implicit conversion of date values passed in to strings as this can lead to varying results in different scenarios.

BrandonGalderisi

That's why you should use datetime parameters and not strings.

learningnet

ASKER

thanks for suggesting me to use parameterized input statement

it is now working fine with System.DateTime.UtcNow

thansk for your help guys !

 Dim query As String = "INSERT INTO LearnItResults (CustomerID, TestID, GiftCodeID, StartTime) " & " VALUES(@CustomerID, @TestID, @GiftCodeID, @StartTime)"
                Dim MyCommand As SqlCommand = New SqlCommand(query, MySqlConnection)
                MyCommand.Parameters.Add(New SqlParameter("@CustomerID", SqlDbType.Int))
                MyCommand.Parameters("@CustomerID").Value = CustomerID
 
                MyCommand.Parameters.Add(New SqlParameter("@TestID", SqlDbType.Int))
                MyCommand.Parameters("@TestID").Value = TestID
 
                MyCommand.Parameters.Add(New SqlParameter("@GiftCodeID", SqlDbType.VarChar))
                MyCommand.Parameters("@GiftCodeID").Value = GiftCodeID
 
                MyCommand.Parameters.Add(New SqlParameter("@StartTime", SqlDbType.DateTime))
                MyCommand.Parameters("@StartTime").Value = System.DateTime.UtcNow
 
                MyCommand.Connection.Open()
                MyCommand.ExecuteNonQuery()
                MyCommand.Connection.Close()

Open in new window

learningnet

ASKER

BrandonGalderisi:
is this what you mean by parameterized input statement?

BrandonGalderisi

Yes. This:

MyCommand.Parameters.Add(New SqlParameter("@StartTime", SqlDbType.DateTime))
MyCommand.Parameters("@StartTime").Value = System.DateTime.UtcNow

passes in a date, and not a string that needs to be converted to a date.