Why does this not work?
-
I have the forloowing code. This code work well when you execute it in Query Analyzer, but when I try try to run it from my .Net Application I get the following error "Error converting data type varchar to datetime."
I have narrowed down the problem to been the SET DATEFORMAT DMY at the beginning of the stored procedure is NOT executing.
Can anyone help?
CREATE PROC USP_CalculateMeetingNoDays @StartDate DATETIME, @EndDate DATETIME
AS
SET DATEFORMAT DMY
DECLARE @NoMeetingDays INT
IF @EndDate < @StartDate
BEGIN
RAISERROR('EndDate must be greater than StartDate', 16, 1)
RETURN
END
SET @NoMeetingDays = DATEDIFF(d, @StartDate, @EndDate)
SELECT @NoMeetingDays AS 'NoMeetingDays'
-
As the error is "Error converting data type varchar to datetime" and I can't see anywhere in your code where you're mixing varchar and datetime vars, I'd have to say you're passing varchar data for the parameters ??
Also, unless this is a simplification of the real stored procedure, why not just do this in your .Net application?
--------------------
Colt 45 - the original point and click interface
-
Seems error happens when SQL Server tries to convert string you suplly as a parameter to datetime value.
You must have different date representation formats on the server and on your local machine.
_____________
Code for TallyGenerator -
Not sure whats going on but I got around it by doing the following and from teh .Net Application, I'm passing the StartDate & EndDate as strings.
CREATE PROC USP_CalculateMeetingNoDays @StartDate VARCHAR(20), @EndDate VARCHAR(20)
AS
DECLARE @NoMeetingDays INT, @RealStartDate DATETIME, @RealEndDate DATETIME
SET @RealStartDate = CONVERT(DATETIME, @StartDate, 103)
SET @RealEndDate = CONVERT(DATETIME, @EndDate, 103)
IF @RealEndDate < @RealStartDate
BEGIN
RAISERROR('EndDate must be greater than StartDate', 16, 1)
RETURN
END
SET @NoMeetingDays = DATEDIFF(d, @RealStartDate, @RealEndDate)
SELECT @NoMeetingDays AS 'NoMeetingDays'
-
How about swapping the dates instead of raising an error and an output parameter instead of a select??
Did you need to consider the holidays in this proc too?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply