|
Jonu2
Starting Member
2 Posts |
 Posted - 2009-11-23 : 23:16:54
|
| Hi, I'm very new to SQL and Crystal Reporting but am trying to enhance an old report so that if no date range is input via the HTML criteria selection then it will default to a fromdate of six months previous and a todate of six months in the future.(Currently this report will produce no data if neither date is specified as they both default to 01-01-1900)The sp used starts with the below code and I only changed the last bit that is highlighted in red. When I run it, it gives me an error indicating that it doesn't know what DATETIME is - it doesn't seem to recognise DATETIME. I know I'm probably doing something very basic wrong but I'd really appreciate being pointed in the right direction please, many thanks:ALTER procedure [dbo].[NCS_AuditReport_Sp]@ServiceCustomerID nvarchar(30),@ResourceID nvarchar(30),-- Auditor@Sector varchar(40), -- Customer Group@State nvarchar(16), -- ServiceStateCode@TaskName varchar(100),@WorkTypeCode varchar(1000),@FromDate datetime,@ToDate datetime,@Standard nvarchar(20) --MOD005as/*** Confidential Information** Limited Distribution of Authorized Persons Only** Created 2005 and Protected as Unpublished Work** Under the U.S. Copyright Act of 1976** Copyright (c) 2001-2005 Epicor Software Corporation** All Rights Reserved**** Rev. Name Date Reference Description** ****** ********** ********** ************** ******************** ** MOD001 TJK 2007-08-28 N/A Re-write the whole thing ** MOD002 TJK 2007-11-30 N/A Correct the join to the Client Infopath tables** MOD003 Marketta R 2009-01-19 Change Division codes to names**** EXAMPLE Procedure CallEXEC "NCS_AuditReport_Sp"NULL,'DPOWER',NULL,NULL,NULL,NULL,'2007-07-01 00:00:000','2007-08-31 00:00:000',NULLEXEC "NCS_AuditReport_Sp"'12023000','DPOWER','522','SA','EMS','40QAS','2007-07-01 00:00:000','2007-08-31 00:00:000','EMS'*/-- Creating a temp table to hold records for the audit reportcreate table #AuditReport ( ProjectCode varchar(40),ServiceCustomerID nvarchar(30),ServiceCustomerName nvarchar(240),BillToID nvarchar(30),BillToName nvarchar(240),Status char,ServiceStandard nvarchar(40),ServiceStateCode nvarchar(16),SectorCode nvarchar(12),SectorName nvarchar(60),ClientManagerID nvarchar(30),ClientManagerFirstName varchar(120),ClientManagerLastName varchar(120),TaskUID int,TaskName nvarchar(120),PlannedHours decimal(8,2),BookedHours decimal(8,2),WorkTypeCode varchar(24),WorkTypeDesc varchar(60),StartDate datetime,EndDate datetime,CompleteFlag int,City nvarchar(50),ProjectPrimaryResource varchar(120), --MOD001)-- MOD001 Update client manager nameselect distinct client_code, client_manager into #ClientManagerFROM ncs_client_certification (NOLOCK)WHERE client_manager IS NOT NULL--MOD007 WHERE Client_code like @ServiceCustomer-- Get WHERE clause parameterdeclare @ServiceCustomer nvarchar(100),@Auditor nvarchar(100),@CustomerGroup varchar(100),@ServiceState nvarchar(100),@TaskDesc varchar(2000),@WorkType nvarchar(200),@Stand nvarchar(100)-- If dates are blank then set dates as 6 months ago to 6 months forwardif (@FromDate = 0 or @FromDate ='' or @FromDate is null or @FromDate = '1900-01-01 00:00:00.000')and (@ToDate = 0 or @ToDate ='' or @ToDate is null or @ToDate = '1900-01-01 00:00:00.000')beginselect dateadd(month,-6,datetime) as StartDateselect dateadd(month,6,datetime) as EndDate end |
|
Datetime error running SQL SP for Crystal Report