-2

Using SQL Server 2005

So I have a datetime field in SQL Server called 'dtJornada'

This query returns incorrect sintax. What would be the correct way of writing it?

select * from B
where dtJornada BETWEEN '2018-01-01' and '2018-01-04'
AND dtJornada NOT IN ['2018-01-01T14:25:10']
Jack Casas
  • 914
  • 18
  • 37
  • 1
    Use parentheses (`()`) not brackets(`[]`) for your `IN` list. But also, 2005 is long out of support, you should really be looking at upgrade paths. – Thom A Apr 03 '19 at 12:35
  • Is the time important or do you just want to exclude a certain date? – Ruan du Preez Apr 03 '19 at 12:36
  • Also, as 2005 doesn't support the `date` data type `BETWEEN` may not provide the results you're after. I would suggest `>=` and `<` logic. – Thom A Apr 03 '19 at 12:37
  • As an example to my above comment, the date and time `'2018-01-04 00:00:00.003'` is **not** between `'2018-01-01'` and `'2018-01-04'`. – Thom A Apr 03 '19 at 12:41
  • And since it's 2005 it also means no `DateTime2` data type - so beware of `yyyy-mm-dd` format, as it's culture dependent when using `DateTime`. Use `yyyymmdd` instead. For more information, [read this.](https://stackoverflow.com/a/55275699/3094533) – Zohar Peled Apr 03 '19 at 12:49

1 Answers1

2

This is wrong:

dtJornada NOT IN ['2018-01-01T14:25:10']

The correct would be:

dtJornada NOT IN ('2018-01-01T14:25:10')

...although if you only want to filter out one value, why use NOT IN? You can just do:

dtJornada <>'2018-01-01T14:25:10'
George Menoutis
  • 6,894
  • 3
  • 19
  • 43