SQL query one day off

Question

I am running a SQL-Query in C# using Dapper & DapperQueryBuilder.

using Dapper;
using DapperQueryBuilder;

I use startDate and endDate as lower and upper date limit, since I am not interested in entries that are fare in past.

string startDate = DateTime.Now.AddDays(-14).ToString("MM/dd/yyyy");
string endDate = DateTime.Now.ToString("MM/dd/yyyy");

The query looks as following:

var originalData = cn.QueryBuilder($@"
            SELECT TOP 10000
               dbo.BEOLStatus_SelCol.Priority,
               dbo.BEOLStatus_SelCol.Stage,
               dbo.BEOLStatus_SelCol.Batch,
               dbo.BEOLStatus_SelCol.Product_Name,
               dbo.BEOLStatus_SelCol.DInStage
            FROM dbo.BEOLStatus_SelCol
            WHERE (((dbo.BEOLStatus_SelCol.Priority) Like {Priority})
            AND ((dbo.BEOLStatus_SelCol.Stage) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.Batch) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.Product_Name) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.DInStage) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.StageNo) Like {StageNo})
            AND ((dbo.BEOLStatus_SelCol.DInStage) >= {startDate})
            AND ((dbo.BEOLStatus_SelCol.DInStage) <= {endDate}))
            ORDER BY dbo.BEOLStatus_SelCol.Priority").Query();

I also tried the following one:

var originalData = cn.QueryBuilder($@"
            SELECT TOP 10000
               dbo.BEOLStatus_SelCol.Priority,
               dbo.BEOLStatus_SelCol.Stage,
               dbo.BEOLStatus_SelCol.Batch,
               dbo.BEOLStatus_SelCol.Product_Name,
               dbo.BEOLStatus_SelCol.DInStage
            FROM dbo.BEOLStatus_SelCol
            WHERE (((dbo.BEOLStatus_SelCol.Priority) Like {Priority})
            AND ((dbo.BEOLStatus_SelCol.Stage) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.Batch) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.Product_Name) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.DInStage) Is Not Null)
            AND ((dbo.BEOLStatus_SelCol.StageNo) Like {StageNo})
            AND ((dbo.BEOLStatus_SelCol.DInStage) Between {startDate} and {endDate}))
            ORDER BY dbo.BEOLStatus_SelCol.Priority").Query();

I can see 7 datasets in the database that fit the following criteria:

Priority
StageNo
between startDate = 09.16.2022 and endDate = 09.30.2022

6 out of the 7 datasets get returned as expected, but one is missing.

The one dataset missing looks as following:

Priority = 3
StageNo = 149
DInStage = 09.30.2022 (#)

(#) actually 30.09.2022 because the database is located in Europe, but based on the other 6 datasets returning I assume it gets internally converted and seems to be working

I figured out that if I change endDate = DateTime.Now.ToString("MM/dd/yyyy") to DateTime.Now.AddDays(+1).ToString("MM/dd/yyyy") I get the 7 datasets that I want.

DInStage >= startDate and DInStage <= endDate also seem to make sense to me.

Does anybody see where I did a mistake that causes this one day off? Why do I need to add one additional day? I notice the same behaviour when using the same query in MS Access. I assume the same is true for dateStart, but I haven't specifically checked that yet.

if `DInStage` is `datetime` then you should include the time part too. Or you could just add one day, and change it from `less than or equal` to `less than`. — iSR5, Sep 30 '22 at 06:43
@iSR5 I think you are right, so I played a little bit around with it. Thanks a lot! — Evolyzer, Oct 03 '22 at 08:53
You should never convert datetime to to string, if it's a Datetime column in the database. — Palle Due, Oct 04 '22 at 14:41

score 0 · Accepted Answer · answered Oct 03 '22 at 09:04

I think I figured out the reason:

I thought that by using ToString("MM/dd/yyyy") I would be solely comparing the Date of startDate/endDate with DInStage
But actually the Time is still considered during the >=/<=/Between

So I switched to ToString("MM/dd/yyyy HH:mm:ss") instead which solved the problem for me. I am using now:

startDate = DateTime.Now.AddDays(-14).ToString("MM/dd/yyyy HH:mm:ss");
endDate = DateTime.Now.AddDays(0).ToString("MM/dd/yyyy HH:mm:ss");

So I think when using this new code now I am actually using the timespan between (lets assume for simplicity that its rn 09:00 on 30.09.2022):

startDate = 16.09.2022 09:00:00
endDate = 30.09.2022 09:00:00

So to be entirely correct I would need to set the 09:00:00 to 00:00:00/23:59:59 in order to get a 100% accurate result. But for my use case that's already close enough.
I assume it can be solved by following the provided solution found at How to change time in DateTime?, but I haven't actually verified it.

SQL query one day off

1 Answers1