为Datetime存储的proc [英] stored proc for Datetime

问题描述

亲爱的所有人，

请查看我传递给DashBoard存储过程的参数.当我将参数作为日期传递时，它可以正常工作，但是当我同时为日期和时间传递参数时，我需要它能够正常工作.
例如，ALTER proc [dbo].[DashBoardTime]''01/01/2011 9:00:00 AM''，''01/25/2011 10:00:00 PM''

Dear All,

Please Have a look at the parameters that I am passing to the DashBoard stored proc. Its working fine when I pass the parameter as date, but I need it to be working when I pass the parameter for both date and time.
e.g., ALTER proc [dbo].[DashBoardTime] ''01/01/2011 9:00:00 AM'',''01/25/2011 10:00:00 PM''

USE [FTA]
GO
/****** Object:  StoredProcedure [dbo].[DashBoard]    Script Date: 02/02/2011 11:20:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[DashBoard] --''01/01/2011'',''01/25/2011''
(
@startdate datetime,
@enddate datetime
)
as
begin
set nocount on
DECLARE @temptable TABLE
(
HubId BIGINT,
HubName NVARCHAR(50),
InwardPass BIGINT,
InwardFail BIGINT
)
INSERT INTO @temptable(HubId,HubName)
--SELECT 0  AS ''HUBId'',''Total'' AS ''HubName'' UNION 
SELECT DISTINCT HUBId,HubName FROM tbl_DataHubView WHERE IsActive=1 

UPDATE @temptable SET InwardPass = 
(select COUNT(InwardID)  FROM tbl_Inward A WHERE A.iDataHubID=B.HubId AND InwardStatus=''Accept'' AND Convert(VARCHAR(50),InwardUpdateTime,101) between Convert(VARCHAR(50),@startdate,101) AND Convert(VARCHAR(50),@enddate,101))
FROM @temptable B
UPDATE @temptable SET InwardFail = 
(select COUNT(InwardID)  FROM tbl_Inward A WHERE A.iDataHubID=B.HubId AND InwardStatus=''Reject'' AND Convert(VARCHAR(50),InwardUpdateTime,101) between Convert(VARCHAR(50),@startdate,101) AND Convert(VARCHAR(50),@enddate,101))
FROM @temptable B


INSERT INTO @temptable(HubId,HubName,InwardPass,InwardFail)
SELECT 0  AS ''HUBId'',''Total'' AS ''HubName'',SUM(InwardPass) AS ''InwardPass'',SUM(InwardFail) AS ''InwardFail''
FROM @temptable
select HubName,InwardPass,InwardFail FROM @temptable
end

有人可以帮我吗?

提前谢谢.

Raj

Can Anyone Help me in this?

Thanks in advance.

Raj

推荐答案

请检查示例:

Please check the example:

<pre lang="sql">Create proc [dbo].[DashBoard] (@startdate datetime,@enddate datetime)
as
begin
DECLARE @temptable TABLE (mystartdate datetime, myEnddate datetime )
insert into @temptable (mystartdate, myEnddate) values (@startdate , @enddate)
select * FROM @temptable
end
go

exec Dashboard ''01/01/2011 9:00:00 AM'',''01/25/2011 10:00:00 PM''

这篇关于为Datetime存储的proc的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！