I'm trying to find invoices in an invoice table but I need only invoices done on saturdays. Is there a way to select specific days between date range. like if my starting date is 2019-01-05 which is a saturday, and to see only the next saturdays so basically every 7 days?
WHERE DATEDIFF(DAY, '20190105',invoiceDate)%7 = 0 AND invoiceDate >= '20190105'
I would build a table of dates - and join to that table. This keeps the query sargable where it can utilize an index if one exists.
Declare @startDate date = '2019-01-05' , @endDate date = '2020-01-10'; With t(n) As ( Select t.n From ( Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0) , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n) ) , dates (inputDate) As ( Select Top ((datediff(day, @startDate, @endDate) + 1) / 7 + 1) dateadd(day, checksum(row_number() over(Order By @@spid) - 1) * 7, @startDate) From t t1, t t2, t t3, t t4 ) Select * , datename(weekday, inputDate) From dates dt Inner Join invoiceTable it On it.InvoiceDate = dt.inputDate;
This is an example - you can build your list of Saturdays - but this requires the InvoiceDate column to be defined as a DATE data type. If it is defined as a datetime and contains a time component other than midnight - then you need to modify the join.
I'd also avoid using a function in the WHERE, and use a join instead, since SQL is optimized for joins and there is that small chance of being able to use an index seek (although I do think that 7,000 yrs worth of tally numbers is extreme overkill).
I also added logic to insure that the starting date is a Saturday, and if not adjust it, likewise for the ending date, while also allowing an ending date of null meaning "current date".
Finally, once you get the starting Saturday, you can just add 7 to get each next Saturday, you don't need a datediff calc every time.
Here's my corresponding code:
DECLARE @start_date date DECLARE @end_date date /*NULL=thru current Saturday*/ SET @start_date = '20190105' SET @end_date = NULL -------------------------------------------------------------------------------- ;WITH cte_tally10 AS ( SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number) ), cte_tally100 AS ( SELECT 0 AS number FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 ), cte_tally10K AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 AS number FROM cte_tally100 c1 CROSS JOIN cte_tally100 c2 ), cte_verify_adjust_dates AS ( SELECT /* if start_date is not a Saturday, back up to the immediately previous Saturday */ DATEADD(DAY, -DATEDIFF(DAY, '19000106', @start_date) % 7, @start_date) AS start_date, /* if end_date is not a Saturday, move forward to the very next Saturday */ DATEADD(DAY, -DATEDIFF(DAY, '19000106', end_date_base) % 7, end_date_base) AS end_date FROM ( SELECT DATEADD(DAY, 6, ISNULL(@end_date, CAST(GETDATE() AS date))) AS end_date_base ) AS end_date_base ) SELECT i.* FROM dbo.invoices i INNER JOIN ( SELECT DATEADD(DAY, weeks.number * 7, start_date) AS saturday FROM cte_verify_adjust_dates INNER JOIN cte_tally10K weeks ON weeks.number BETWEEN 0 AND DATEDIFF(DAY, vad.start_date, vad.end_date) / 7 ) AS saturdays ON saturdays.saturday = i.invoice_date ORDER BY i.invoice_date
What data type is your invoice date column and you only want the next Saturday or all Saturdays?
Good day yosiasz
the type is date in that format : YYYY-MM-DD HH:MM:SS.****(milliseconds)
and it's all saturday withing a specifi range
This looks like the column is defined as a datetime data type - you can execute sp_help on the invoice table to get the actual data types for the columns.
If the values in that column contain times - for example: 2020-06-22 08:30:30.333 then you need to account for that in the join statement.
The way to do that is:
Inner Join invoiceTable it On it.InvoiceDate >= dt.inputDate And it.InvoideDate < dateadd(day, 1, dt.inputDate
You would also modify the following:
Declare @startDate datetime = '2019-01-05' , @endDate datetime = '2020-01-10';
Just to be clear - my solution is not generating 7000 years worth of dates. It allows for generating that many - but will only generate the number of rows needed.