November 15, 2007 at 8:02 am
I'm getting the error converting the data type to datetime.....where did I go wrong?
CREATE PROCEDURE dbo.gm_P4P_PQRI_MEARSURE_8
(
@STARTDATE DATETIME = NULL,
@ENDDATE DATETIME = NULL,
@CPT varchar(15) = NULL,
@MODIFIER varchar(255) = NULL,
@PTYPE varchar(255) = NULL,
@doctor varchar(255) = NULL,
@FACILITY varchar(255) = NULL,
@COMPANY varchar(10)
)
AS
SET NOCOUNT ON
BEGIN
SELECT
@CPT = CASE @CPT WHEN '' THEN NULL ELSE @CPT END,
@MODIFIER = CASE @MODIFIER WHEN '' THEN NULL ELSE @MODIFIER END,
@PTYPE = CASE @PTYPE WHEN '' THEN NULL ELSE @PTYPE END,
@doctor = CASE @doctor WHEN '' THEN NULL ELSE @doctor END,
@FACILITY = CASE @FACILITY WHEN '' THEN NULL ELSE @FACILITY END,
@COMPANY = CASE @COMPANY WHEN '' THEN NULL ELSE @COMPANY END
IF NOT @StartDate IS NULL AND NOT @EndDate IS NULL
BEGIN
IF ISDATE(@StartDate)=1 AND ISDATE(@EndDate)=1
BEGIN
SELECT DISTINCT
CLMASTER.SSNO, CLMASTER.PFNAME, CLMASTER.PLNAME, CLMASTER.PMNAME, CLCHARGE.MODIFIER, CLCHARGE.PTYPE, CLCHARGE.RDOC,
CLCHARGE.ICD9, CLCHARGE.CPT, MWAPPTS.ADATE, CLCHARGE.FACILITY,
(SELECT TOP 1 vartext FROM cldictionary WHERE ssno = clmaster.ssno AND section = 'P4PG' AND varcode = 'EXRXBB' AND Status = 'Y'ORDER BY Reported DESC) AS Exclusion
FROM
MWAPPTS INNER JOIN
CLCHARGE ON MWAPPTS.COMPANY = CLCHARGE.COMPANY AND MWAPPTS.ACCOUNT = CLCHARGE.ACCOUNT INNER JOIN
CLMASTER ON MWAPPTS.COMPANY = CLMASTER.COMPANY AND MWAPPTS.ACCOUNT = CLMASTER.ACCOUNT
WHERE (CLMASTER.SSNO IN (SELECT ssno FROM cldictionary WHERE SECTION = 'P4PG' AND VARCODE = 'RXBB')) AND (CLMASTER.SSNO IN (SELECT ssno FROM cldictionary WHERE SECTION = 'P4PG' AND VARCODE = 'DXCHF'))
AND CLCHARGE.CPT = ISNULL(@CPT, CLCHARGE.CPT)
AND CLCHARGE.MODIFIER = ISNULL(@MODIFIER, CLCHARGE.MODIFIER)
AND CLCHARGE.PTYPE = ISNULL(@PTYPE, CLCHARGE.PTYPE)
AND CLCHARGE.RDOC = ISNULL(@DOCTOR, CLCHARGE.RDOC)
AND CLCHARGE.FACILITY = ISNULL(@FACILITY, CLCHARGE.FACILITY)
AND CLCHARGE.COMPANY = ISNULL(@COMPANY, CLCHARGE.COMPANY)
AND MWAPPTS.ADATE BETWEEN CAST(Convert(varchar(100), @StartDate, 101) as DateTime) AND CAST(Convert(varchar(100), @EndDate, 101) as DateTime)
END
ELSE
BEGIN
RAISERROR('The date values provided are not capable of being converted.', 16, 1)
RETURN
END
END
ELSE
BEGIN
SELECT DISTINCT
CLMASTER.SSNO, CLMASTER.PFNAME, CLMASTER.PLNAME, CLMASTER.PMNAME, CLCHARGE.MODIFIER, CLCHARGE.PTYPE, CLCHARGE.RDOC,
CLCHARGE.ICD9, CLCHARGE.CPT, MWAPPTS.ADATE, CLCHARGE.FACILITY,
(SELECT TOP 1 vartext FROM cldictionary WHERE ssno = clmaster.ssno AND section = 'P4PG' AND varcode = 'EXRXBB' AND Status = 'Y'ORDER BY Reported DESC) AS Exclusion
FROM
MWAPPTS LEFT OUTER JOIN
CLCHARGE ON MWAPPTS.COMPANY = CLCHARGE.COMPANY AND MWAPPTS.ACCOUNT = CLCHARGE.ACCOUNT LEFT OUTER JOIN
CLMASTER ON CLCHARGE.COMPANY = CLMASTER.COMPANY AND CLCHARGE.ACCOUNT = CLMASTER.ACCOUNT
WHERE (CLMASTER.SSNO IN (SELECT ssno FROM cldictionary WHERE SECTION = 'P4PG' AND VARCODE = 'RXBB')) AND (CLMASTER.SSNO IN (SELECT ssno FROM cldictionary WHERE SECTION = 'P4PG' AND VARCODE = 'DXCHF'))
AND CLCHARGE.CPT = ISNULL(@CPT, CLCHARGE.CPT)
AND CLCHARGE.MODIFIER = ISNULL(@MODIFIER, CLCHARGE.MODIFIER)
AND CLCHARGE.PTYPE = ISNULL(@PTYPE, CLCHARGE.PTYPE)
AND CLCHARGE.RDOC = ISNULL(@DOCTOR, CLCHARGE.RDOC)
AND CLCHARGE.FACILITY = ISNULL(@FACILITY, CLCHARGE.FACILITY)
AND CLCHARGE.COMPANY = ISNULL(@COMPANY, CLCHARGE.COMPANY)
END
END
SET NOCOUNT OFF
GO
November 15, 2007 at 8:29 am
What are the values you are passing into the stored procedure? Can you post that code as well?
And what data type is the column MWAPPTS.ADATE ?
November 15, 2007 at 8:29 am
This could be due to the date conversion happening from string to date in the where clause
AND MWAPPTS.ADATE BETWEEN CAST(Convert(varchar(100),
@StartDate, 101) as DateTime) AND CAST(Convert(varchar(100),
@EndDate, 101) as DateTime)
Date conversion to 101 format always returns date in mm/dd/yy format.
To convert mm/dd/yy back to date the dateformat, the current session should be in mdy format.
Try to set the dateformat at the beginning of SP as
SET DATEFORMAT MDY
SET DATEFORMAT MDY
SELECT CAST(Convert(varchar(100), GETDATE(), 101) as DateTime) AS TODAY
TODAY
-----------------------
2007-11-15 00:00:00.000
The followinge example shows, if you have dmy set as date format, date conversion fails.
SET DATEFORMAT DMY
SELECT CAST(Convert(varchar(100), GETDATE(), 101) as DateTime)
Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Hope this helps
November 15, 2007 at 8:30 am
>>IF ISDATE(@StartDate)=1 AND ISDATE(@EndDate)=1
The variables @StartDate and @EndDate are already true datetime datatypes, so this IF statement is redundant.
>>AND MWAPPTS.ADATE BETWEEN CAST(Convert(varchar(100), @StartDate, 101) as DateTime) AND CAST(Convert(varchar(100), @EndDate, 101) as DateTime)
What is the datatype of the database column MWAPPTS.ADATE ?
[Edit] Nevermind, too slow on the draw today
November 15, 2007 at 8:38 am
the datatype is datetime for mwappts.adate however the problem lies with the script being used within a crystal report and I can't have a datetime format...I just need date. So when the parameter within the report comes up it gives start of range as date of 2007/11/15
November 15, 2007 at 8:41 am
Ahh, if it's Crystal passing the parameters, maybe the parameter declarations need to be changed:
CREATE PROCEDURE dbo.gm_P4P_PQRI_MEARSURE_8
(
@STARTDATE VARCHAR(20) = NULL,
@ENDDATE VARCHAR(20) = NULL,
Declare params as varchars, let Crystal pass in whatever, then validate if Crystal has given you valid dates.
November 15, 2007 at 8:47 am
I presume the stored procedure does not even get executed because the date value passed into the date parameters fails the conversion.
Can't you format the date value in the report to conform to SQL's date format or connection setting?
November 15, 2007 at 8:47 am
Crystal didn't like that either....
November 15, 2007 at 9:05 am
actually at this point I don't care about it being datetime or not...crystal won't even pull the start date or end date correctly.....any help would be appricated.
November 15, 2007 at 9:10 am
Almost sounds more like a crystal problem than a SQL problem.
You could pass the date value as a string to the stored procedure and then convert the string to proper date format before you use it in the rest of the stored procedure. But that is not advised. Crystal may be sending the date format according to the client's computer's date setting, so the date may differ from PC to PC. If you want to go this route, format the date in a fixed format, then pass it as a string to the stored procedure.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply