SQLTeam.com | Weblogs | Forums

Convert Date into Default format


#1

Hi
I'm passing the date through parameter when I run the update statement it generated the error

Error Unhandled exception has occurred....
The conversion of a varchard data type to a smalldatetime datatype resulted in an out of range values

the values from the string = CustomerRefString is from following code

CustomerRefString = DateTimePicker1.Value.Date
CustomerRefString = CustomerRefString.Replace(vbCr, "").Replace(vbLf, ",")
' VisitIDString = VisitIDString.Replace(",", "")
MsgBox(CustomerRefString)
CustomerRefString = CustomerRefString.Replace(" ", "")
MsgBox(CustomerRefString)
CustomerRefString = CustomerRefString.TrimEnd(MyComma)
MsgBox(CustomerRefString)

myCmd1.CommandText = "UPDATE tblContractSite SET DateInActive = '" & CustomerRefString & "' WHERE cardcolumn IN (" & CardNumString & ")"

The output from above code through MsgBox () shown below

UPDATE tableTarget SET DateInActive = 20/10/2017' where cardcolumn in ('1234')

The field 'DateInActive' type is datetime
Appreciate your help
Kind regards,
Farhan


#2

That is missing a single quote (in front of the string-date) - but I doubt that would raise that message

that is at odds with the message which seems to suggest it is SMALLdatetime:

but that may be the result of a cascade-error

Personally I never provide string-dates in formats like "dd/mm/yyyy" as they are subject to SQL Parsing rules, which are ambiguous and highly liable to unexpected behaviour - they depend on, for example, the Language of the currently connected user ...

Better to use "yyyymmdd" which is unambiguous in all instances.


#3

Done
`

myCmd1.CommandText = "UPDATE tblContractSite
SET DateInActive =
Right('" & CustomerRefString & "',4)+Substring('" & CustomerRefString & "',4,2)+Left('" & CustomerRefString & "',2)
WHERE CardNumber IN (" & CardNumString & ")"

or
`

declare @targetdate varchar(10) = '22/10/2017';
update tblContractSite set DateInActive=
convert(datetime,RIGHT(@targetdate,4)+substring(@targetdate,4,2)+left(@targetdate,2))
where CardNumber in (135721)

`

`


#4

Did it fix the problem ?


#5

This method of concatenating a string to be sent to SQL Server for execution is ripe for a SQL Injection attack and should be avoided.

There are many other ways of performing this action - but the best option would be to utilize a stored procedure. If you don't want to go that route - then build your command text with parameters, add the parameters - then execute. That will also insure that what is sent to SQL Server is a date/time regardless of the client formatted string.

From this excellent article on dynamic SQL: http://www.sommarskog.se/dynamic_sql.html

cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = _
" SELECT O.OrderID, SUM(OD.UnitPrice * OD.Quantity)" & _
" FROM dbo.Orders O " & _
" JOIN dbo.[Order Details] OD ON O.OrderID = OD.OrderID" & _
" WHERE O.OrderDate BETWEEN @from AND @to" & _
" AND EXISTS (SELECT *" & _
" FROM dbo.[Order Details] OD2" & _
" WHERE O.OrderID = OD2.OrderID" & _
" AND OD2.ProductID = @prodid)" & _
" GROUP BY O.OrderID"

cmd.Parameters.Add("@from", SqlDbType.Datetime)
cmd.Parameters("@from").Value = "1998-02-01"

cmd.Parameters.Add("@to", SqlDbType.Datetime)
cmd.Parameters("@to").Value = "1998-02-28"

cmd.Parameters.Add("@prodid", SqlDbType.Int)
cmd.Parameters("@prodid").Value = 76

This will insure that appropriate data types are passed to the code - and you don't have to worry about placing single-quotes around your parameters and you avoid SQL injection issues.