Sql: Get Records Created in Time Range for Specific Dates

Specific Time Range Query in SQL Server

I'm assuming you want all three of those as part of the selection criteria. You'll need a few statements in your where but they will be similar to the link your question contained.

SELECT *
  FROM MyTable
  WHERE [dateColumn] > '3/1/2009' AND [dateColumn] <= DATEADD(day,1,'3/31/2009') 
        --make it inclusive for a datetime type
    AND DATEPART(hh,[dateColumn]) >= 6 AND DATEPART(hh,[dateColumn]) <= 22 
        -- gets the hour of the day from the datetime
    AND DATEPART(dw,[dateColumn]) >= 3 AND DATEPART(dw,[dateColumn]) <= 5 
        -- gets the day of the week from the datetime

Hope this helps.

How do I write a SQL query for a specific date range and date time using SQL Server 2008?

Infact this worked for me

 SELECT * 
 FROM myTable
 WHERE CAST(ReadDate AS DATETIME) + ReadTime BETWEEN '2010-09-16 5:00PM' AND '2010-09-21 9:00AM'

Query for records that were active during a specified date range

First, you can turn the original list of timestamps:


ItemID Valid StartDate
------ ----- ----------
A      1     2015-01-01
B      0     2015-01-01
B      1     2017-03-01
C      1     2015-01-01
C      0     2017-04-01
D      0     2015-01-01
D      1     2017-05-01
D      0     2017-06-01
E      1     2015-01-01
E      0     2017-05-01
E      1     2017-06-01
F      1     2015-01-01
F      0     2018-02-01
G      1     2017-12-31
V      0     2015-01-01
V      1     2018-02-01
W      1     2015-01-01
W      0     2016-01-01
X      0     2015-01-01
Y      1     2018-01-01
Z      1     2015-01-01
Z      0     2017-01-01

into a list of ranges, where the end date is either the item's next entry's StartDate or, if the current row is the last entry, today's date:


ItemID  Valid  StartDate   EndDate
------  -----  ----------  ----------
A       1      2015-01-01    (today)
B       0      2015-01-01  2017-03-01
B       1      2017-03-01    (today)
C       1      2015-01-01  2017-04-01
C       0      2017-04-01    (today)
D       0      2015-01-01  2017-05-01
D       1      2017-05-01  2017-06-01
D       0      2017-06-01    (today)
E       1      2015-01-01  2017-05-01
E       0      2017-05-01  2017-06-01
E       1      2017-06-01    (today)
F       1      2015-01-01  2018-02-01
F       0      2018-02-01    (today)
G       1      2017-12-31    (today)
V       0      2015-01-01  2018-02-01
V       1      2018-02-01    (today)
W       1      2015-01-01  2016-01-01
W       0      2016-01-01    (today)
X       0      2015-01-01    (today)
Y       1      2018-01-01    (today)
Z       1      2015-01-01  2017-01-01
Z       0      2017-01-01    (today)

You can use the LEAD analytic function to achieve that:

EndDate = LEAD(StartDate, 1, CAST(CURRENT_TIMESTAMP AS date))
          OVER (PARTITION BY ItemID ORDER BY StartDate ASC)

Once you have a list of ranges, it is easy to match the rows by using this established method of finding intersecting ranges (the ranges in the tables intersecting with the range specified in the query parameters):

StartDate < @EndDate AND EndDate > @StartDate

Here is the complete solution:

DECLARE
  @StartDate date = '2017-01-01',
  @EndDate   date = '2018-01-01',
  @ValidValue bit = 1
;

WITH
  ranges AS
  (
    SELECT
      ItemID,
      Valid,
      StartDate,
      EndDate = LEAD(StartDate, 1, CAST(CURRENT_TIMESTAMP AS date))
                OVER (PARTITION BY ItemID ORDER BY StartDate ASC)
    FROM
      #Temp
  )
SELECT DISTINCT
  ItemID
FROM
  ranges
WHERE
  Valid = @ValidValue
  AND StartDate < @EndDate
  AND EndDate > @StartDate
;

You can play with this method in this demo at db<>fiddle.

Note: After completing my answer I realised that it ended up being very similar to Sami's. The difference is in handling the items' last entries.

SQL: Get records created in time range for specific dates

In Oracle we can turn dates into numbers and apply arithmetic to them in a variety of ways.

For instance sysdate-7 gives us the date seven days ago. trunc(some_date) removes the time element from a date column. And to_char(some_date, 'SSSSS') gives us its time element as the number of seconds since midnight. So 06:45:00 is 24300 seconds and 18:15:59 is 69359 seconds (please check those numbers, as they are back-of-an-envelope figgerin').

Anyway, putting that all together in a single query like this ...

select *
from your_table
where creation_date >= trunc(sysdate)-7
and to_number(to_char(creation_date, 'sssss')) between 24300 and 69359

... wil produce all the records created in the last week with a time element within core hours.

SQL query to fetch records within a time range for particular date range/period

I'd try with something like this:

select *
from your_Table
where date_column >= date '2019-04-01'
  and date_column <  date '2019-04-10'
  and to_number(to_char(date_column, 'hh24miss')) between 120000 and 130000;

Get data in a specific date for a time range with a minute difference

f I do AddMinutes(1) to the endDate

Not sure why you'd add a whole minute when the problem is caused by additional milliseconds and can be solved by going up a second..

Do you actually want to keep the milliseconds on the dates? Is it any use to you to know that the event occurred at 12:34.56.789 rather than 12:34:56? If not, make the column a datetime2(0) to discard the milliseconds permanently

Otherwise I recommend the route several other people are also advocating and do your LINQ like

context.Whatever.Where(x => x.CreatedAt >= startDate && x.CreatedAt < endDate.AddSeconds(1))

The "less than" is vital. A "less than 12:34:57" will get 12:34:56.999999... which appears to be what you want with your "less than or equal to 12:34:56"

If you're struggling to understand why milliseconds cause a problem, think of dates like numbers - if you have a number 1.222222 and you ask the db for "less than or equal to 1.2" the DB doesn't auto-round the 1.222222 down to 1 decimal place and then go "oh it's equal to 1.2" and return it. It just goes "1.222222 is not less than or equal to 1.2, don't return it"

Over time for question like this you'll always get someone who says "just cast it to.." or "run this to calculate a new date to remove the milliseconds..." - don't; if you write a query that manipulates the table data, that manipulation has to be performed every time the data is queried. It typically kills the ability for the db to use an index on the column too (the db will probably switch to running the manipulation on every value in the index, every time) which means the query is massively more resource intensive.

Always consider manipulating table data in a where clause as an absolute last resort. If there is no other way and the query will be heavily used, look at adding some kind of calculated column to the table and index it so that, conceptually, the result of the manipulation you're carrying out is done once and an index on it can be used

How to Select record between a given time range and Item Category

Consider concatenating string/date values in the CDate() function available in Jet/ACE ODBC as well as the Access.exe UI:

sql = "SELECT id, item 
         FROM Table1
        WHERE category ='toys' 
          AND CDate(adddate & ' ' & addtime) > CDate('5/23/2016 8:00 AM')
          AND CDate(adddate & ' ' & addtime) <= CDate('5/24/2016 3:00 PM')

Alternatively, use BETWEEN operator (which includes end points, so I add a second):

sql = "SELECT id, item 
         FROM Table1
        WHERE category ='toys' 
          AND CDate(adddate & ' ' & addtime) 
          BETWEEN CDate('5/23/2016 8:01 AM') AND CDate('5/24/2016 3:00 PM')

How to select rows for a specific date, ignoring time in SQL Server

You can remove the time component when comparing:

SELECT * 
FROM sales 
WHERE CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, salesDate))) = '11/11/2010'

Another approach is to change the select to cover all the time between the start and end of the date:

SELECT * 
FROM sales 
-- WHERE salesDate BETWEEN '11/11/2010 00:00:00.00' AND '11/11/2010 23:59:59.999'
WHERE salesDate BETWEEN '2020-05-18T00:00:00.00' AND '2020-05-18T23:59:59.999'