Menu
I just want a quick way (and preferably not using a while loop)of createing a table of every date between date @x and date @y so I can left outer join to some stats tables, some of which will have no records for certain days in between, allowing me to mark missing days with a 0
663
SELECT * FROM ATM WHERE TRANSACTION_TIME BETWEEN '2001-02-01 10:00:00' AND '2007-03-01 22:00:00';
SELECT * FROM YourTable. WHERE [dateColumn] >DATEADD(day,1,'4/25/2022') AND [dateColumn] <= DATEADD(day,1,'4/26/2022') AND DATEPART(hh,[dateColumn]) >= 7 AND DATEPART(hh,[dateColumn]) <= 19.
Use the DATEDIFF() function to retrieve the number of days between two dates in a MySQL database. This function takes two arguments: The end date. (In our example, it's the expiration_date column.)
The SQL BETWEEN OperatorThe values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
Strictly speaking this doesn't exactly answer your question, but its pretty neat.
Assuming you can live with specifying the number of days after the start date, then using a Common Table Expression gives you:
WITH numbers ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM numbers WHERE n < 500 )
SELECT DATEADD(day,n-1,'2008/11/01') FROM numbers
OPTION ( MAXRECURSION 500 )
I would create a Calendar table that just contained every date from a suitable start date until a suitable end date. This wouldn't take up much space in your database and would make these types of query child's play.
select ...
from Calendar
left outer join
...
where Calendar.Date >= @x
and Calendar.Date <= @y
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
