Helo All -
I would greatly appreciate some help formatting the values for my sproc.
I need to call a sproc and pass it the values StartTime and EndTime from a web form. The web form uses 3 DDLs for the Start Hour, Start Minute and Start AM/PM as well as 3 DDLs for the End Hour, End Minute and End AM/PM.
When I call the sproc, I get the error:
SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Here is the sample column data: 2:06 PM which is stored as nvarchar in the sql db
Here is the Sub and the Sproc,
Sub
Protected Sub btnDateQuery_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnDateQuery.Click
pnlFiltered.Visible =
True
pnlShowAll.Visible =
FalseDim StartTime As DateTime
Dim EndTime As DateTimeStartTime = ddlFromHour.SelectedValue + ":" + ddlFromMinute.SelectedValue + ":" + "00 " + ddlFromAMPM.SelectedValue
EndTime = ddlToHour.SelectedValue +
":" + ddlToMinute.SelectedValue + ":" + "00 " + ddlToAMPM.SelectedValueStartTime = CDate("2:07:00 AM")
EndTime =
CDate("2:07:00 AM")Dim Query_TA As New dalDataFeedsdefsTableAdapters.spFilterExpectedEndTimeTableAdapterDim returncode As Integer = 0
Query_TA.GetExpectedEndTimeData(StartTime, EndTime)
Dim Select_TA As New dalDataFeedsdefsTableAdapters.spDynamic_Basics_TblTableAdapterDim dv As New DataView
Try
dv = Query_TA.GetExpectedEndTimeData(StartTime, EndTime).DefaultView()
Catch ex As Exception
Response.Write(StartTime +
" - " + EndTime)
End TryWith DataFeedGridView
.DataSource = dv
.DataBind()
End With
End Sub
SPROC
ALTER PROCEDURE [dbo].[spFilterExpectedEndTime]
-- Add the parameters for the stored procedure here
@StartTime
datetime,@EndTime datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select * From tmpBasics
where
convert(datetime, ltrim(rtrim(starttime)), 114) >= @StartTime and dateadd(mi,convert(int, duration), convert(datetime, ltrim(rtrim(starttime)), 114)) <= @EndTime
order
by convert(datetime, ltrim(rtrim(starttime)), 114)
END
Thanks,
Pat