Datetime in Where Clause

Using DATETIME in WHERE clause in SQL Server

You cannot refer to a column alias in the where clause. The typical solutions are to use subqueries or CTEs. But SQL Server has another method that I like, apply:

SELECT [MsgNumber], [StateAfter], v.dt
FROM TABLE t CROSS APPLY
     (VALUES (DATETIMEFROMPARTS(SUBSTRING ([TimeString], 7, 4), SUBSTRING([TimeString], 4, 2), SUBSTRING([TimeString], 1, 2),
                    SUBSTRING([TimeString], 12, 2), SUBSTRING( [TimeString], 15, 2), SUBSTRING([TimeString], 18, 2), 0)
             )
     ) V(dt)
WHERE [MsgNumber] IN (5, 9, 13, 17) AND
      v.dt > DATETIMEFROMPARTS(2018, 4, 9, 0, 0, 0, 0) AND
      v.dt <  DATETIMEFROMPARTS(2018, 5, 9, 0, 0, 0, 0))
ORDER BY dt ASC,StateAfter ASC;

I find it curious that you don't use the simpler:

WHERE [MsgNumber] IN (5, 9, 13, 17) AND
      v.dt > '20180409'  AND
      v.dt < '20180509'

(I did not use hyphens because this format will always be interpreted as YYYYMMDD regardless of internationalization settings.)

Using datetime index in where clause MySQL

All of your queries would take advantage of an index on column created_at. MySQL always uses an index when it matches the predicate(s) of the where clause.

The output of your explains do indicate that you do not have this index in place, which is confirmed by the output of your create table.

Just create the index and your database will use it.

Here is a demo:

-- sample table, without the index
create table mytable(id int, created_at datetime);

--  the query does a full scan, as no index is available
explain select created_at from mytable where created_at >= '2020-05-15 23:00:00';

id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra      
-: | :---------- | :------ | :--------- | :--- | :------------ | :--- | :------ | :--- | ---: | -------: | :----------
 1 | SIMPLE      | mytable | null       | ALL  | null          | null | null    | null |    1 |   100.00 | Using where

-- now add the index
create index idx_mytable_created_at on mytable(created_at);

-- the query uses the index
explain select created_at from mytable where created_at >= '2020-05-15 23:00:00';

id | select_type | table   | partitions | type  | possible_keys          | key                    | key_len | ref  | rows | filtered | Extra                   
-: | :---------- | :------ | :--------- | :---- | :--------------------- | :--------------------- | :------ | :--- | ---: | -------: | :-----------------------
 1 | SIMPLE      | mytable | null       | index | idx_mytable_created_at | idx_mytable_created_at | 6       | null |    1 |   100.00 | Using where; Using index

Why does using CONVERT(DATETIME, [date], [format]) in WHERE clause take so long?

Going to summarise my comments here, as they are "second class citizens" and thus could be removed.

Firstly, the reason your query is slow is because of theCONVERT on the column DATE in your WHERE. Applying functions to a column in your WHERE will almost always make your query non-SARGable (there are some exceptions, but that doesn't make them a good idea). As a result, the entire table must be scanned to find rows that are applicable for your WHERE; it can't use an index to help it.

The real problem, therefore, is that you are storing a date (and time) value in your table as a non-date (and time) datatype; presumably a (n)varchar. This is, in truth, a major design flaw and needs to be fixed. String type values aren't validated to be valid dates, so someone could easily insert the "date" '20210229' or even 20211332'. Fixing the design not only stops this, but also makes your data smaller (a date is 3 bytes in size, a varchar(8) would be 10 bytes), and you could pass strongly typed date and time values to your query and it would be SARGable.

"Fortunately" it appears your data is in the style code 112, which is yyyyMMdd; this at least means that the ordering of the dates is the same as if it were a strongly typed date (and time) data type. This means that the below query will work and return the results you want:

SELECT TOP 10 * --Ideally don't use * and list your columns properly
FROM dbo.[Table]
WHERE [DATE] >= '20210704' AND [DATE] < '20210705'
ORDER BY {Some Column};

How to SELECT year using a WHERE clause on a DateTime type column

It is not correct as it includes data from 2014-01-01, which isn't a day in 2013.

Instead of between, which checks for a closed interval, use >= and < to search for an interval open at the end.

SELECT *
       FROM sales
       WHERE sales_date >= '2013-01-01'
             AND sales_date < '2014-01-01';

You could also use year() or datepart() to extract the year like in

...
WHERE year(sales_date) = 2013;

or

...
WHERE datepart(year, sales_date) = 2013;

But that will prevent any index on sales_date to be used, which isn't good in terms of performance. So it's not the best way to query that data, the first approach with >= and < is to be preferred.

How to query DATETIME field using only date in Microsoft SQL Server?

use range, or DateDiff function

 select * from test 
 where date between '03/19/2014' and '03/19/2014 23:59:59'

or

 select * from test 
 where datediff(day, date, '03/19/2014') = 0

Other options are:

1. If you have control over the database schema, and you don't need the
   time data, take it out.

2. or, if you must keep it, add a computed column attribute that has the time portion of the date value stripped off...

Alter table Test
Add DateOnly As
DateAdd(day, datediff(day, 0, date), 0)

or, in more recent versions of SQL Server...

Alter table Test
Add DateOnly As
Cast(DateAdd(day, datediff(day, 0, date), 0) as Date)

then, you can write your query as simply:

select * from test 
where DateOnly = '03/19/2014'

Using a date value in where clause on a datetime data type

If you pass a string like '20190604' to a datetime then yes, it'll be interpreted as the date and time 2019-06-04T00:00:00.000. This means that if you have any values that aren't on the stroke of midnight then the row won't be returned (even if that value is 2019-06-04T00:00:00.003).

If you do have values other than at midnight and you want to return all rows for a specific date you can use CONVERT/CAST to change the data type of the column to date; which is still SARGable:

SELECT DateField
FROM CalendarTbl
WHERE CONVERT(date,DateField) = '20190604';

How to convert Datetime to Date and use in Where clause?

You can use cast(CreatedDate as Date) to convert. But I don't think you need to convert in this case to compare.

Just do:

WHERE CreatedDate between '2016-06-01' and '2016-06-31'
should be fine

Using Where clause for DateTime field using a LINQ statement

Since your db table column format datetime, just try to use function

var dt = new DateTime(2022,01,01);
....

 &&    EF.Functions.DateDiffYear(policy.PolicyEnd, dt) >= 0

or since you are checking only year you can try to use the whole data, sometimes it works

var dt =  new DateTime(2021, 12, 31).AddDays(1).AddTicks(-1);
...

 &&  policy.PolicyEnd > dt

Date in where clause returning 0 rows

yyyy-mm-dd hh:mm:ss.000 is not a language- and regional settings-safe format!

SELECT CONVERT(datetime, '2021-09-07');
SET LANGUAGE Nederlands;
SELECT CONVERT(datetime, '2021-09-07');

Results (db<>fiddle):

-----------------------
2021-09-07 00:00:00.000

-----------------------
2021-07-09 00:00:00.000

Please use yyyy-mm-ddThh:mm:ss.000 - that T is extremely important. So:

WHERE DateCommande = '2018-09-05T00:00:00.000';

Or in your case, probably just something closer to what you have in your question, not what you have in your screenshot:

WHERE DateCommande = '20180905'; -- YYYYMMDD *not* YYYY-MM-DD

But unless you only want those rows without time or are sure there is never going to be a time associated with the date (in which case, why isn't the data type date?), neither of those queries will be safe. Better to say:

WHERE DateCommande >= '20180905'
  AND DateCommande <  '20180906';

See the links in the section entitled Regional formats here, for a lot more background:

• Dating responsibly

---

Related Topics