SQL query not returning correct date range

Question

I have a simple view created in VS 2017. Here it is:

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)

As you can see, it gets data between 2 dates and does some simple aggregation.

The idea of the cast is that I want to ignore the time and get everything for the date range regardless - so as of today, 15th Mar, I would it to fetch everything for 14th March 00:00:00 - 23:59:59 and 29 days previous.

This does not happen - it picks up 3 rows (13th) - it should pick up all 5 rows. And yes, my system date is currently 15/03/2018 14:44 (UK time).

Here's, the table and data:

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

You can CAST your CreatedOn field as DATE to remove the time portion, which is getting in your way here...

Perhaps

WHERE CAST(CreatedOn AS DATE) BETWEEN CAST(GETDATE()-30 AS date) AND CAST(GETDATE()-1 AS date)

BUT - CASTing a field in the WHERE expression may make it non SARGable. See here. So avoid this solution for large or production environments unless you know the expression will be SARGable. Use only as a test to refine your logic and options. (Even if there is no explicit index on CreatedOn - it may still suffer as SQL builds its own indexes all the time if no index exists explicitly. Always worth confirming whether it is SARGable so you know for sure.)

To see what is happening - view your values in your SELECT - just to get an idea of what is working

For example:

SELECT TOP 1000
CreatedOn 
,CAST(GETDATE()-30 AS date)
,CAST(GETDATE()-1 AS date)
FROM [Application]

Or see the other options for removing time values from datatime fields here as you may want to coerce or round the time value instead

Answer 2

Instead of trying to ignore the time value, just make sure that your search terms are accurate for it. Also, don't blindly add things like ISNULL to every column. Spend a few seconds thinking if it's relevant or not. NEWID() for example, is never going to return a NULL value to you. Adding that kind of code is poor programming which will lead to less legible code.

Here's how I would write it to account for the time portions:

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

Answer 3

Try this out:

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

The problem is your converting to date the day before today.