2

I'm querying a SQL Server DB from VB.Net with below command but i cannot get it to select rows from a certain date as the column contains datetime values. It keeps saying

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I've tried CAST & CONVERT and various date functions but i cant get it to work

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/03/2019 00:00:01' AND '27/03/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

I'm expecting rows on a given date but im receiving no rows at all. One line of data does have a date on the 27/03/2019 so this should be returned

David Little
  • 21
  • 1
  • use SET DATEFORMAT dmy; https://learn.microsoft.com/en-us/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-2017 – Kemal AL GAZZAH Mar 27 '19 at 14:29
  • Rather than trying to get close to the end of a day, you should either `Cast( RaceStartTime as Date )` to eliminate the time-of-day or use a [half-open interval](https://en.wikipedia.org/wiki/Interval_(mathematics)#Terminology) with the subsequent date, e.g. `'2019-03-27' <= RaceStartTime and RaceStartTime < '2013-03-28'` where the second comparison is `<`, not `<=`. PSA: [ISO date format](https://xkcd.com/1179/). – HABO Mar 27 '19 at 14:45
  • 1
    @HABO [beware of `datetime` and `yyyy-mm-dd`](https://stackoverflow.com/questions/45792766/date-conversion-and-culture-difference-between-date-and-datetime) and also [this one](https://stackoverflow.com/a/55275699/3094533) – Zohar Peled Mar 27 '19 at 14:59

2 Answers2

0

The only reason you'd get this error is if your server is set to think of 27 as the month and 03 as the date instead of the other way around.

You should use locale-neutral strings like 'YYYYMMDD'

Tangential to the issue causing the error, another, less error-prone way, to get rows for a specific day is like this:

WHERE CONVERT(date, RaceStartTime) = '20190327'
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • ... Or even better, avoid using string representations of datetime altogether, if possible. – Zohar Peled Mar 27 '19 at 14:05
  • Appreciate the quick reply, can you advise how i would implement a locale-neutral string in the context of this query ? – David Little Mar 27 '19 at 14:08
  • Sorry im new to SQL and it still doesn't work, it doesnt produce an exception but it doesnt return the one row im expecting it to ```Dim CmdText As String = "SELECT * FROM Races m WHERE CONVERT(date, RaceStartTime) = '20190327' AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"``` – David Little Mar 27 '19 at 14:12
  • 1
    [Cast to date is sargable but is it a good idea?](https://dba.stackexchange.com/questions/34047/cast-to-date-is-sargable-but-is-it-a-good-idea) tl;dr; : No it's not. – Zohar Peled Mar 27 '19 at 14:15
  • @DavidLittle That's because your subquery gets the `MIN(RaceTime)` for that driver of ALL days, not the `MIN` time for '20190327'. Add the same condition to the subquery and it should work. – Tab Alleman Mar 27 '19 at 14:27
  • @ZoharPeled Good to know! In this particular instance, the performance difference wouldn't be noticeable, but in an operation involving thousands of records, it's worth knowing. – Tab Alleman Mar 27 '19 at 14:31
0

Did you get any error? also try without the Reactime?

CmdText As String = "SELECT * FROM Races m WHERE (RaceStartTime BETWEEN '27/Mar/2019 00:00:01' AND '27/Mar/2019 23:59:59') AND RaceTime = (SELECT MIN(RaceTime) FROM Races WHERE DriverName = m.DriverName) ORDER BY RaceTime;"

Can you try this query from Sql Server Management Studio first? I Think the issue is might be for RaceTime??

Hasan Mahmood
  • 978
  • 7
  • 10
  • https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common And those date literals make my head spin, additionally they will exclude some rows because of the odd times you put on there. – Sean Lange Mar 27 '19 at 14:20