Select data from date range between two dates and times

Question

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'

Not working how? If you keep only one condition, does that query work? — jarlh, Dec 14 '17 at 11:48
I am just getting a syntax error Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','. Would it matter that I am using a view instead of the table? — Conger88, Dec 14 '17 at 11:50
As mentioned in Jaydip Jadhav's answer: you got the syntax for `BETWEEN` wrong. It is `where datecolumn between '2017-01-01T00:00:00.000' and '2017-12-31T23:59:59.999'`. — Thorsten Kettner, Dec 14 '17 at 12:01
@ThorstenKettner could you post the solution? I have tried and still no luck :/ — Conger88, Dec 14 '17 at 12:17

Caius Jard · Accepted Answer · 2017-12-14T12:40:11.760

1

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

edited Dec 14 '17 at 12:40

answered Dec 14 '17 at 12:01

Caius Jard

72,509
5
49
80

I just tried this and I am getting this error The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. – Conger88 Dec 14 '17 at 12:16
If the explicit conversions I've just edited in don't work out, it's probably time for you to show us the data types of startDate, leftDate, start_date and left_dept_date – Caius Jard Dec 14 '17 at 12:31
Its actually only arrival_Date and left_dept_date, typo above. they are both of datetime null. is this what you needed? – Conger88 Dec 14 '17 at 15:36
This looks to be working! just doing some validation on it now but it is running – Conger88 Dec 14 '17 at 15:42
Can I ask another one on this? The query above gets me all users that were in depart between them times. I have a task to get all the users that are in the department at 8 am for each month. Could I tweak the code above to get this? – Conger88 Dec 14 '17 at 16:29
I didn't understand the typo comment, but if there is an error in the query where a column name (like startDate) is wrong (it should be arrival_date) then you can just edit `startdate` -> `arrival_date` and the query will work out. I'm also not sure I understand "in at 8am each month" - months don't have times, but days do. Did you mean "i want a report of all employees who always come to work at 8am" (== "show all employees who get to work on time") ? It's a different question, really, because it's asking for a row-by-row analysis of the time part of a date, to check that it's always < 8am – Caius Jard Dec 14 '17 at 17:06
Thanks Caius, the question I wanted to answer was 'average number of users who are in dept at 8am for each month over the past 12 months'. – Conger88 Dec 15 '17 at 17:23
And start_date is the time that they clocked in, each day? Or is it startdate? Why are there two? – Caius Jard Dec 15 '17 at 19:41
no and start_Date and startdate are the same. That was my mistake. so they could have started at 7:45 and left at 8:05 so include these. stared at 8:05 and left at 9:05 incclude these. started at 7:45 and left at 9:05 etc basically all scenarios of when they were in department at 8 – Conger88 Dec 18 '17 at 16:33
is this possible? – Conger88 Dec 21 '17 at 11:37
It is, and it's probably simpler to exclude people who left before 8:00:00 or who came after 8:59:59 as this amounts to the same thing but is fewer logical conditions. In SQLserver terms it's `WHERE NOT(DATEPART(hour, enddate)<8 OR DATEPART(hour, startdate)>8)` datepart returns the hour they clocked in/put it they checked in at 9:01 they're excluded. If they clocked out at 7:59 they're excluded. Otherwise they should be included – Caius Jard Dec 21 '17 at 12:41
I am on break now but thanks I will try this shortly and get back to you. Thanks @Caius Jard – Conger88 Dec 27 '17 at 01:31
Thanks Caius that works brilliantly. I assume then to just get the average for each month would be just to calculate each month one at a time? Using where arrival dates > set date and less than set date etc? – Conger88 Jan 02 '18 at 12:24
There are ways to get results month by month for an entire year, decade etc but it would be best perhaps to ask a new SO question, put the workign SQL you have right now, in the question and say that it's for just one month but you'd like to know how to do it in one hit for every month of the year.. The solution will probably use a CROSS APPLY or CROSS JOIN against a function or query that generates a bunch of month date values instead of using a single month in a where clasuse- it's a lot of work ot explain in a single comment how it'd work tho, so hence my suggestion to ask a new Q – Caius Jard Jan 03 '18 at 17:23
Thats fair enough. Thanks again for all your help Caius. Il ask a new question – Conger88 Jan 04 '18 at 18:26

Jaydip Jadhav · Answer 2 · 2017-12-14T12:06:32.943

0

I am not sure about syntax for BETWEEN Clause is upgraded in sql Server 2012. but it seems like that you have syntax error in between clause

SELECT * 
FROM aview 
WHERE ( startDate BETWEEN '2017-11-25 11:27:00.000' AND '2018-11-25 11:27:00.000' AND
        leftdate  BETWEEN  '2017-11-25 11:27:00.000' AND '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'

edited Dec 14 '17 at 12:06

answered Dec 14 '17 at 11:51

Jaydip Jadhav

12,179
6
24
40

I have just tried in sql server 2005 and same error – Conger88 Dec 14 '17 at 11:57
1

Conger88: *Same* error? Incorrect syntax near ','? I don't see any comma in this query. – Thorsten Kettner Dec 14 '17 at 11:59
SELECT * FROM attendance_view WHERE arrival_Date BETWEEN ('2017-11-25 00:00:00.000', '2018-11-25 00:00:00.000') OR left_dept_date BETWEEN ('2017-11-25 00:00:00.000', '2018-11-25 00:00:00.000') OR (arrival_Date <= '2017-11-25 00:00:00.000' AND left_dept_date >= '2018-11-25 00:00:00.000') – Conger88 Dec 14 '17 at 12:02
Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','. – Conger88 Dec 14 '17 at 12:02
@Conger88 Try the above script. the script you have trying is have synatx error. – Jaydip Jadhav Dec 14 '17 at 12:03
@JaydipJadhav no your solution didn't work either. I am getting a Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. – Conger88 Dec 14 '17 at 12:06
SQL Server doesn't support ANSI timestamp literals, but wants strings instead properly formatted. Maybe it's just a T missing: Try `'2017-11-25T11:27:00.000'` instead of `'2017-11-25 11:27:00.000'` etc. – Thorsten Kettner Dec 14 '17 at 12:56
1

Jaydip Jadhav's query runs fine in my test: http://rextester.com/EESN27897 – Thorsten Kettner Dec 14 '17 at 13:06

score 0 · Answer 3 · answered Dec 14 '17 at 13:16

0

You copied the wrong answer. The BETWEEN syntax is invalid.

Then if you want to know whether a record's range overlaps with a given range, why are there four columns in your table and query? It should be two: the start and the end.

SELECT *
FROM aview 
WHERE start <= '2018-11-25T11:27:00.000' AND end >= '2017-11-25T11:27:00.000';

Or if you want the record's whole range within the given range:

SELECT *
FROM aview 
WHERE start >= '2017-11-25T11:27:00.000' AND end <= '2018-11-25T11:27:00.000';

answered Dec 14 '17 at 13:16

Thorsten Kettner

89,309
7
49
73

are you sure this captures all scenarios? If you see the image on pafe https://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates/36354313#36354313 this perfectly describes my exact problem(The image with the whiteboard) – Conger88 Dec 14 '17 at 15:34

score 0 · Answer 4 · edited Jul 25 '20 at 08:50

0

SELECT *  FROM USERContracts C
WHERE   
(C.UCFromDate > ='2008-01-01' AND C.uCFromDate <='2020-02-06' )
OR (C.UCToDate < ='2008-01-01' AND C.uCToDate >='2020-02-06')

order by USERID

edited Jul 25 '20 at 08:50

Dima Kozhevin

3,602
9
39
52

answered Jul 25 '20 at 08:10

Miftah amin

19
1
7

While this code may solve the question, [including an explanation](//meta.stackexchange.com/q/114762) of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please [edit] your answer to add explanations and give an indication of what limitations and assumptions apply. – Adrian Mole Jul 25 '20 at 14:55

Select data from date range between two dates and times

4 Answers4