Sql Server Table date query showing incorrect result

sql server datetime comparison not working
how is datetime stored in sql server
what are the implementation problems of dates and times data types?
replace date in sql
sql cast date
sql server same query returns different results

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.

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'

Top 5 Reasons for Wrong Results in SQL Server, 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. A small result set may contain a single row. A large result set splits the long JSON string across multiple rows. By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. The SSMS status bar displays the actual row count.

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'`

FIX: Incorrect data returned when you query the DATE column in , SQL queries are often constructed within application code or dynamic Since the server is set up with US English, SQL Server has to interpret that as September 8th. Moral: Always use proper date/time parameters, and whenever using a Let's say we have a simple table with a Unicode column, and we  Assume that you have a table that contains a DATE data type column, and you have implemented Clustered Column Store Index (CCSI) on this table in Microsoft SQL Server 2014. When you try to query the table by using the DATE data type as a qualifier, incorrect data may be returned. For example: create table tab2 (c1 int, c2 date, c3 varchar(10))

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.

How to Get SQL Server Dates and Times Horribly Wrong, in Microsoft SQL Server 2014. When you try to query the table by using the DATE data type as a qualifier, incorrect data may be returned. How to query data in a System-Versioned Temporal Tables in SQL Server February 20, 2017 by Marko Zivkovic This article will cover the querying of temporal tables in SQL Server by using the FOR SYSTEM_TIME clause and its four sub clauses AS OF, FROM TO, BETWEEN AND, CONTAINED IN.

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

Expert SQL Server 2005 Development, Failure #1: Assuming that SQL Server stores date/time values as formatted date/time values often result from not understanding how SQL Server The following table shows the results returned by the SELECT statement:  Create a table in SQL Server 2012 SP1 named "Test," and insert a record in to the table: CREATE TABLE TEST (I DATE); INSERT INTO TEST VALUES (GETDATE()); Run the following queries to return a correct result: SELECT TOP 1 TRY_CONVERT(VARCHAR, TRY_CONVERT(DATE, '20121023'), 112) FROM TEST;

Why Queries May Easily Return Incorrect Data in SQL Server, Running this query, you will find that the correct data is returned; you'll see all rows As a result, this query is unable to seek an index, and SQL Server is forced to of the table, convert each ADate value to a string, and then compare it to the date string. This produces the execution plan shown in Figure 10-1, which has an  This is generally based on time and date. A sure-fire way of getting managers upset is to get the figures horribly wrong by messing up the way that you handle datetime values in SQL Server. In the interests of peace, harmony and a long career in BI, Robert Sheldon outlines some of the worst mistakes you can make when using SQL Server dates.

SQL Server CURRENT_TIMESTAMP Function, Let's create a quick revenue table. We are going to create some sample data to quickly count in SQL Server. Here's the code to set it up:. APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse. You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

Error Message 1 to 500, SQL Server Functions The CURRENT_TIMESTAMP function returns the current date and time, in a 'YYYY-MM-DD hh:mm:ss.mmm' format. Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse,  When you run a query in a Microsoft Access database that joins linked Microsoft SQL Server tables from different sources or databases, the query may not return any data. However, when you run a similar query that refers to the linked SQL Server tables from the same source or database, this problem does not occur.

Comments
  • 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….
  • That should probably be a half-open interval 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, 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.
  • In SQL Server even YYYY-MM-DD can be affected by the locale.. This reminded me though that the ODBC date literal also works