Sql Server存储过程按日期过滤?
问题描述:
我是新来编写存储过程,所以我有点卡住了 - 我想编写一个过程,我想按日期过滤。我有一个保存日期时间的列,但我只想匹配日期时间值中的日期部分。我该如何解决它?这是我到目前为止所尝试的。 我有一个名为trxdate的类型为datetime的列。Sql Server存储过程按日期过滤?
USE [CAFFE]
GO
/****** Object: StoredProcedure [dbo].[p_paymentrecordsbydateandpaymentmode] Script Date: 3/3/2017 11:32:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[p_paymentrecordsbydateandpaymentmode]
(
@Country VARCHAR(20),
@BankID VARCHAR(20),
@ModeOfPayment VARCHAR(20),
@TrxDate datetime
)
AS
SELECT
MobileNumber,
ModeOfPayment,
Branch,
ReceiptNo,
Amount,
TrxDate
FROM tb_TransactionRequests(NOLOCK)
WHERE @ModeOfPayment=ModeOfPayment and @TrxDate = CONVERT(VARCHAR(10),TrxDate,111)
答
尝试修改WHERE子句这样的:
WHERE @ModeOfPayment=ModeOfPayment and @TrxDate = cast(TrxDate as date)
,并传递参数的程序,
@TrxDate date
PROC通话将如下
[dbo].[p_paymentrecordsbydateandpaymentmode] 1,1,'modeOfPayment','20170303'
答
你实际上不需要如果存储过程转换为varchar ure参数和表数据库类型是datetime。正如下面
ALTER Procedure [dbo].[p_paymentrecordsbydateandpaymentmode]
(
@Country VARCHAR(20),
@BankID VARCHAR(20),
@ModeOfPayment VARCHAR(20),
@TrxDate datetime
)
AS
SELECT
MobileNumber,
ModeOfPayment,
Branch,
ReceiptNo,
Amount,
TrxDate
FROM tb_TransactionRequests(NOLOCK)
WHERE @ModeOfPayment=ModeOfPayment and @TrxDate = TrxDate
答
您可以通过以下CAST
行动从datetime
场隔离日期/时间:
-- Select date
SELECT CAST(GETDATE() AS DATE);
-- Select time
SELECT CAST(GETDATE() AS TIME);
@ DmitryCat-如何更改的程序,这样我可以的日期内提取记录范围内并获取该日期内的数据? –
如果我真的了解你,你需要在\ @ date1和\ @ date2之间尝试投射(TrxDate作为日期) –