Select data from date range between two dates and times

I have pretty much the exact same question as logged here Select data from date range between two dates

But when I used the solution given it does not work for me. Only difference is that my table has date and time in the one column. So I want to be able to return all values that fall within the date range

I have this so far but not working

SELECT * FROM aview 
WHERE startDate BETWEEN ('2017-11-25 11:27:00.000', '2018-11-25 11:27:00.000') OR 
leftdate BETWEEN ('2017-11-25 11:27:00.000', '2018-11-25 11:27:00.000') OR 
start_Date <= '2017-11-25 11:27:00.000' AND left_dept_date >= '2018-11-25 11:27:00.000'

Answer

I’ve never seen BETWEEN(from,to) as a pattern in an sql query – you’re making it look like a function call similar to SUBSTRING(column, index) when it doesn’t work like that. Try this:

SELECT * FROM aview 
WHERE 
  startDate BETWEEN CONVERT(datetime, '2017-11-25 11:27:00.000', 121) AND CONVERT(datetime, '2018-11-25 11:27:00.000', 121) OR 
  leftdate BETWEEN CONVERT(datetime, '2017-11-25 11:27:00.000', 121) AND CONVERT(datetime, '2018-11-25 11:27:00.000', 121) OR 
  start_Date <= CONVERT(datetime, '2017-11-25 11:27:00.000', 121) AND left_dept_date >= CONVERT(datetime, '2018-11-25 11:27:00.000', 121)

There’s no syntax error with this query (see http://sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/16114 ) when the columns are proper dates, so the only thing left is a flaw in the view code, doing a bum conversion of data – for example if your view is converting a string of “12/31/2017” into a date, but doing it as if it was “dd/mm/yyyy”, or perhaps converting a “2107-12-31” with a typo in the year, into a smalldatetime type that doesn’t support years beyond 2079

See https://learn.microsoft.com/en-us/sql/t-sql/language-elements/between-transact-sql