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'
Advertisement
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