sql - SQL查询未返回正确的日期范围

Question

我在VS 2017中创建了一个简单的 View 。在这里:

CREATE VIEW [dbo].[ApplicantStat]
    AS SELECT ISNULL(CONVERT(VARCHAR(50), NEWID()), '') AS ID,
    ISNULL(AVG(ApplicationTime), 0) AS 'AvgApplicationTime',
    ISNULL(AVG(ResponseTime), 0) AS 'AvgResponseTime',
    ISNULL(CAST(COUNT(CASE WHEN [IsAccepted] = 1 THEN 1 END) / COUNT(CASE WHEN [IsValid] = 1 THEN 1 END) AS float), 0) AS 'PctAccepted'
    FROM [Application]
    WHERE CreatedOn BETWEEN CAST(GETDATE()-30 AS date) AND CAST(GETDATE()-1 AS date)

如您所见，它获取2个日期之间的数据并进行一些简单的汇总。

强制转换的想法是，我想忽略时间并获得日期范围内的所有内容-从今天开始，即3月15日，我希望获取3月14日的所有内容00:00:00-23:59:59还有29天之前

这不会发生-它会拾取3行(第13行)-应该会拾取所有5行。是的，我的系统日期当前是15/03/2018 14:44(英国时间)。

表格和数据如下:

CREATE TABLE [dbo].[Application] (
    [Id]              INT            NOT NULL,
    [ApplicantId]     INT            NOT NULL,
    [LoanAmount]      INT            NOT NULL,
    [LoanTerm]        SMALLINT       NOT NULL,
    [EmailAddress]    VARCHAR (254)  NOT NULL,
    [MobilePhone]     VARCHAR (11)   NOT NULL,
    [House]           VARCHAR (25)   NOT NULL,
    [Street]          VARCHAR (50)   NOT NULL,
    [TownCity]        VARCHAR (50)   NOT NULL,
    [Postcode]        VARCHAR (7)    NOT NULL,
    [IpAddress]       VARCHAR (39)   NOT NULL,
    [IsValid]         BIT            NOT NULL,
    [IsAccepted]      BIT            NOT NULL,
    [Commission]      DECIMAL (9, 2) NOT NULL,
    [Processors]      VARCHAR (500)  NOT NULL,
    [ResponseTime]    SMALLINT       NOT NULL,
    [ApplicationTime] SMALLINT       NOT NULL,
    [CreatedOn]       DATETIME       NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

INSERT INTO [dbo].[Application] ([Id], [ApplicantId], [LoanAmount], [LoanTerm], [EmailAddress], [MobilePhone], [House], [Street], [TownCity], [Postcode], [IpAddress], [IsValid], [IsAccepted], [Commission], [Processors], [ResponseTime], [ApplicationTime], [CreatedOn]) VALUES (1, 1, 300, 3, N'john.doe@tmail.com', N'07957000000', N'1', N'Acacia Avenue', N'Suburbia', N'SB1 2RB', N'100.100.100.100', 1, 1, CAST(3.20 AS Decimal(9, 2)), N'1,2,3,4,5', 90, 600, N'2018-03-13 08:00:00')
INSERT INTO [dbo].[Application] ([Id], [ApplicantId], [LoanAmount], [LoanTerm], [EmailAddress], [MobilePhone], [House], [Street], [TownCity], [Postcode], [IpAddress], [IsValid], [IsAccepted], [Commission], [Processors], [ResponseTime], [ApplicationTime], [CreatedOn]) VALUES (2, 2, 500, 12, N'a@b.com', N'0', N'1', N'a', N's', N's', N'1', 0, 1, CAST(5.00 AS Decimal(9, 2)), N'1', 60, 300, N'2018-03-14 16:00:00')
INSERT INTO [dbo].[Application] ([Id], [ApplicantId], [LoanAmount], [LoanTerm], [EmailAddress], [MobilePhone], [House], [Street], [TownCity], [Postcode], [IpAddress], [IsValid], [IsAccepted], [Commission], [Processors], [ResponseTime], [ApplicationTime], [CreatedOn]) VALUES (3, 3, 1000, 6, N'a@b.com', N'0', N'1', N'a', N's', N's', N'1', 1, 1, CAST(7.00 AS Decimal(9, 2)), N'1', 75, 360, N'2018-03-13 10:00:00')
INSERT INTO [dbo].[Application] ([Id], [ApplicantId], [LoanAmount], [LoanTerm], [EmailAddress], [MobilePhone], [House], [Street], [TownCity], [Postcode], [IpAddress], [IsValid], [IsAccepted], [Commission], [Processors], [ResponseTime], [ApplicationTime], [CreatedOn]) VALUES (4, 4, 2000, 24, N'a@b.com', N'0', N'1', N'a', N's', N's', N'1', 1, 1, CAST(20.00 AS Decimal(9, 2)), N'1', 30, 365, N'2018-03-14 11:00:00')
INSERT INTO [dbo].[Application] ([Id], [ApplicantId], [LoanAmount], [LoanTerm], [EmailAddress], [MobilePhone], [House], [Street], [TownCity], [Postcode], [IpAddress], [IsValid], [IsAccepted], [Commission], [Processors], [ResponseTime], [ApplicationTime], [CreatedOn]) VALUES (5, 5, 3000, 18, N'a@b.com', N'0', N'1', N'a', N's', N's', N'1', 1, 1, CAST(40.00 AS Decimal(9, 2)), N'1', 45, 330, N'2018-03-13 12:00:00')

Answer 1

试试看:

WHERE 
    CreatedOn >= CAST(GETDATE()-30 AS date) AND 
    CreatedOn < CAST(GETDATE() AS date)

问题是您将日期转换为今天的前一天。