Skip to content

Instantly share code, notes, and snippets.

@GabrielMMelo

GabrielMMelo/days_between_2_dates.sql

Created March 2, 2021 17:54
Show Gist options
  • Save GabrielMMelo/662608dab7c9dc05f53b48bc2bf55f79 to your computer and use it in GitHub Desktop.
Save GabrielMMelo/662608dab7c9dc05f53b48bc2bf55f79 to your computer and use it in GitHub Desktop.
Download ZIP
T-SQL to get all days between two dates (without variables or recursives CTE)
Raw
days_between_2_dates.sql
WITH
min_date AS
(
SELECT TOP 1 DATE_FIELD FROM TABLE_XPTO ORDER BY DATE_FIELD ASC
),
max_date AS
(
SELECT TOP 1 DATE_FIELD FROM TABLE_XPTO ORDER BY DATE_FIELD DESC
)
SELECT
TOP (DATEDIFF(DAY, (SELECT MAX(DATE_FIELD) FROM min_date), (SELECT MAX(DATE_FIELD) FROM max_date)) + 1)
Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, (SELECT MAX(DATE_FIELD) FROM min_date))
FROM
sys.all_objects a
CROSS JOIN
sys.all_objects b;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment