Passing a date to a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Passing a date to a stored procedure

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2004-11-04 : 05:04:44

I am entering a date in a textbox in UK format DD/MM/YY and passing it to a stored procedure. However, it is not returning my records. if I enter it as MM/DD/YY it works. So how do I convert the date so my stored procedure will run. Here's my code and SP.

Private Sub txtDatetoDisplay_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtDatetoDisplay.TextChanged
Dim senderTextBox As TextBox = CType(sender, TextBox)

'Check Input
Dim SqlDataAdapter1 As New SqlDataAdapter
Dim MySQL As String = "spRMU_GetMyRequestsbyDate"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strRMULoginName", Me.Session("LoginUsername")))
Cmd.Parameters.Add(New SqlParameter("@strRMUDateRequested", CDate(senderTextBox.Text)))
SqlDataAdapter1.SelectCommand = Cmd
SqlDataAdapter1.Fill(DsOsRequests1, "vweSeeMyRequests")
Me.Label12.Text = DsOsRequests1.Tables("vweSeeMyRequests").Rows.Count
Me.LboxOSRequests.DataBind()

End Sub

CREATE Procedure spRMU_GetMyRequestsbyDate

@strRMULoginname nvarchar (100),
@strRMUDateRequested datetime

as

Select *
from vweSeeMyRequests
where LoginName = @strRMULoginname
and DateRequested=@strRMUDateRequested
GO

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-04 : 05:06:09

try this before you run the sproc

set dateformat dmy

edit: if you have the time part in your data then you need to check that also. or use:
where yourDate = DATEADD(Day, DATEDIFF(Day, 0, yourDate), 0)

Go with the flow & have fun! Else fight the flow

Pinto
Aged Yak Warrior

590 Posts

Posted - 2004-11-04 : 05:20:39

Thank you - with set dateformat dmy in my sp that works fine now. I didn't have the time as well.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-04 : 05:30:48

I would pass the date as a string yyyymmdd to make sure the app isn't region dependent.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources