Sql Server Table date query showing incorrect result

Question

I have a Sql server table which contains below Date values(4th october)

Now Below query is not showing any result

  select 
            *
        from [dbo].[TB_AUDIT] TBA 

        where   TBA.ActionDate >= '10/01/2018' and TBA.ActionDate <= '10/04/2018' which is not correct.

But If I write select * from [dbo].[TB_AUDIT] TBA

    where   TBA.ActionDate >= '10/01/2018' and TBA.ActionDate <= '10/05/2018' it is returning me all results.

What I am doing wrong.

Is that January 10 or October 1st? If you ask for dates between January and April, you won't get results from October. Don't use localized strings. The only unambiguous *date* format is `YYYYMMDD`. The unambiguous datetime format is the full ISO8601, ie `YYYY-MM-DDTHH:mm:ss….` — Panagiotis Kanavos, Oct 04 '18 at 15:55

Eric Brandt · Answer 1 · 2018-10-04T16:18:09.853

3

When you don't specify a time component for a DATETIME, SQL Server defaults it to midnight. So in your first query, you're asking for all results <='2018-10-04T00:00:00.000'. All of the data points in your table are greater than '2018-10-04T00:00:00.000', so nothing is returned.

You want

TBA.ActionDate >= '2018-10-01T00:00:00.000' and TBA.ActionDate < '2018-10-05T00:00:00.000'`

edited Oct 04 '18 at 16:18

answered Oct 04 '18 at 16:01

Eric Brandt

7,617
2
17
34

That should probably be a [half-open interval](https://en.wikipedia.org/wiki/Interval_(mathematics)#Terminology) with the `ActionDate` less-then, but not equal to, the end date + 1. (Based on the apparent intent of the OP's first query.) – HABO Oct 04 '18 at 16:17
@HABO, absolutely correct. I was lazy and copied & pasted from the question to get the field name and missed modifying the operator. Edited, and thanks for the +1. – Eric Brandt Oct 04 '18 at 16:19

score 3 · Accepted Answer · answered Oct 04 '18 at 16:09

There are two problems with this query. The first, is that it's using a localized string. To me, it looks like it's asking for rows between January and April. The unambiguous date format is YYYYMMDD. YYYY-MM-DD by itself may not work in SQL server as it's still affected by the language. The ODBC date literal, {d'YYYY-MM-DD'} also works unambiguously.

Second, the date parameters have no time which defaults to 00:00. The stored dates though have a time element which means they are outside the search range, even if the date parameter was recognized.

The query should change to :

select 
        *
from [dbo].[TB_AUDIT] TBA 
where   
    cast(TBA.ActionDate as date) between '20181001' and '20181004'

or

    cast(TBA.ActionDate as date) between {d'2018-10-01'} and {d'2018-10-04'}

Normally, applying a function to a field prevents the server from using any indexes. SQL Server is smart enough though to convert this to a query that covers the entire date, essentially similar to

where   
    TBA.ActionDate >='2018:10:01T00:00' and TBA.ActionDate <'2018-10-05T00:00:00'

score 1 · Answer 3 · answered Oct 04 '18 at 15:58

1

Use properly formatted dates!

select *
from [dbo].[TB_AUDIT] TBA 
where TBA.ActionDate >= '2018-10-01' and TBA.ActionDate <= '2018-10-04'

YYYY-MM-DD isn't just a good idea. It is the ISO standard for date formats, recognized by most databases.

answered Oct 04 '18 at 15:58

Gordon Linoff

1,198,228
53
572
709

In SQL Server even `YYYY-MM-DD` can be affected by the locale.. [This reminded me though](https://stackoverflow.com/questions/33228765/how-do-i-specify-date-literal-when-writing-sql-query-from-sql-server-that-is-lin) that the ODBC date literal also works – Panagiotis Kanavos Oct 04 '18 at 15:58

score 0 · Answer 4 · answered Oct 04 '18 at 16:29

0

when you just filter by the date, it is with regard to the time as per the standard.

answered Oct 04 '18 at 16:29

lije

420
2
12

Sql Server Table date query showing incorrect result

4 Answers4