Comparisons of dates in SQL Server

Question

Comparisons of dates in SQL Server

Navigation

#1 by (1 votes)

1

I am trying to make a query between a range of dates, and when I use the> = to today's date (11/26/2018) if it shows me, instead if I use only the = to the date of today it does not show me anything, when it is not logical because there is no data with a later date. I leave the code:

SELECT
    substring(maquina_id,1,5), 
    count(distinct [rep].[id])
  FROM [matLot]
  LEFT JOIN [rep] on [matLot].[lot] = [rep].[id]
  where [fechaEntrada] >= cast('26/11/2018' AS datetime)
  group by substring([maquina_id],1,5)
  order by substring([maquina_id],1,5)

sql-server

asked by marcss 26.11.2018 в 11:23

source

1 answer

Problem loading image Remove the comma in a double. Android studio

score 1 · Accepted Answer

If you compare fields with the datetime type, you are comparing the day and the time, to compare only by date the datetime field as date. I leave an example.

Schema (MySQL v5.7)

CREATE TABLE test (
  id INT,
  fecha DATETIME
);
INSERT INTO test (id, fecha) VALUES (1, '2018-11-26 01:00:00');
INSERT INTO test (id, fecha) VALUES (1, '2018-11-26 03:00:00');
INSERT INTO test (id, fecha) VALUES (1, '2018-11-27 01:00:00');

Comparing fields with datetime > = we will obtain all records with date and time greater than or equal to the start of the comparison day, in this example '2018-11-26 00:00:00'.

Query # 1

SELECT * FROM test
WHERE fecha >= CAST('2018-11-26' AS datetime)
;

| id  | fecha               |
| --- | ------------------- |
| 1   | 2018-11-26 01:00:00 |
| 1   | 2018-11-26 03:00:00 |
| 1   | 2018-11-27 01:00:00 |

To obtain all the records of a day we compare the data as date .

Query # 2

SELECT * FROM test
WHERE CAST(fecha AS date) = CAST('2018-11-26' AS date)
;

| id  | fecha               |
| --- | ------------------- |
| 1   | 2018-11-26 01:00:00 |
| 1   | 2018-11-26 03:00:00 |

View on DB Fiddle