Date Conversion!

[Adam Knight]

Hi all,

I am trying to pass a date parameter into an sql (SQL SERVER) stored
procedure.

Initially i was trying to pass the parameter like so:

cmdReportQuestions.Parameters.Add(New
SqlParameter("@sdt",Request.Form("txtStartDt")))

This is obviously incorrect as it is being recieved by my stored procedure
as nvarchar.
Thus in my stored procedure i recieve a can not convert data type error.

How do convert this value to an acceptable data type? (VB.NET)
The SQL SERVER parameter data type is SMALLDATETIME ?

Also, the date is in dd/mm/yyyy format.
Does VB.NET have any formatting functions to convert it to mm/dd/yyyy?

Cheers,
Adam

 

[David Browne]

Hi all,

I am trying to pass a date parameter into an sql (SQL SERVER) stored
procedure.

Initially i was trying to pass the parameter like so:

cmdReportQuestions.Parameters.Add(New
SqlParameter("@sdt",Request.Form("txtStartDt")))

Check the other constructors for SqlParameter, and inform it of the
parameter type, or convert the parameter value to a date before you pass it
in (and it will figure the type out).

David

 

[Guest]

try to convert the date to double is easier for u.

 

[Patirck Ige]

Thats it as David adviced
so what is telling you to do is something like this:-
Dim parameterDOB As SqlParameter = New SqlParameter("@DOB",
SqlDbType.SmallDateTime, 4)
parameterDOB.Value = DOB
myCommand.Parameters.Add(parameterDOB)

Or since you are saying its in datatype nvarchar then trying converting it
to datetime before using the above

You can also convert to date time using the below sample snippet
Dim DOB As String = Request.QueryString("DOB")
If Not DOB Is Nothing Then
Dim format As New System.Globalization.CultureInfo("en-gb",
True)
Dim DateOfBirth As DateTime = DateTime.Parse(DOB, format)
End If

And of course you can convert using the FORMAT fucntion like so(lets say you
have a label control):-
lblDOB.Text = Format(myOrderDetails.OrderDate, ("dd/MM/yyyy"))

Hope that helps
Patrick
** How is the weather in QLD?

 

[Adam Knight]

Hi Patrick,

I like the look of the format function, but can't seem to find any info on
the parameters it recieves.
Can you direct me to some info..?

Weather:
Getting a little to hot for my liking, expecting 37 C tommorrow apparently..

Adam

 

[Patirck Ige]

Are you talking about this format:-
Dim DateOfBirth As DateTime = DateTime.Parse(DOB, format)
The DOB is the querystring params and you are parsing it as a DATETIME
The format is just setting it as a en-gb culture
OR do you mean this one lblDOB.Text = Format(myOrderDetails.OrderDate,
("dd/MM/yyyy"))
For instance if you param is :- Request.Form("txtStartDt")
the you can do:-
StartDt = Format(Request.Form("txtStartDt"),("dd/MM/yyyy"))
Hope that helps
Patrick