6
I need to display all dates that exist between two set dates.
I do not need the information of the difference between these dates, nor the data that are in the interval between them, but show the dates for each day of the interval.
Example:
Data de inicio: 01/07/2015
Data de fim: 05/07/2015
Resultado:
01/07/2015
02/07/2015
03/07/2015
04/07/2015
05/07/2015
9
From what I understand in your question the date 30/07/2015 would not enter, and the result would be:
01/07/2015
02/07/2015
03/07/2015
04/07/2015
05/07/2015
Follows code that picks up the date and the interval between them.
declare @startDate date;
declare @endDate date;
select @startDate = '20150701';
select @endDate = '20150705';
with dateRange as
(
select dt = @startDate
where @startDate < @endDate
union all
select dateadd(dd, 1, dt)
from dateRange
where dateadd(dd, 1, dt) <= @endDate
)
select convert(varchar(MAX),dt,103)
from dateRange
3
A common practice in Data Warehouses is the creation of a dimension (usually a table) with all possible dates.
You can create a table with all dates in your database and recover the result by a query simple:
SELECT date FROM allDates WHERE date BETWWEN 20150401 AND 20150501;
The advantage of using this type of table is in the performance gain if you need to make large joins with that range of dates.
You can create this table using a script and if you wish you can insert more columns according to your need.
Follows a script for example:
BEGIN TRY
DROP TABLE [dbo].[DimDate]
END TRY
BEGIN CATCH
/*No Action*/
END CATCH
/**********************************************************************************/
CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
[FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
[DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
[DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
[DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
[DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
[DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),-- Week Number of Month
[WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
[WeekOfYear] VARCHAR(2),--Week Number of the Year
[Month] VARCHAR(2), --Number of the Month 1 to 12
[MonthName] VARCHAR(9),--January, February etc
[MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),--First,Second..
[Year] CHAR(4),-- Year value of Date stored in Row
[YearName] CHAR(7), --CY 2012,CY 2013
[MonthYear] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE,
[IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
[IsWeekday] BIT,-- 0=Week End ,1=Week Day
[HolidayUSA] VARCHAR(50),--Name of Holiday in US
[IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday
[HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
)
GO
/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date
DECLARE @StartDate DATETIME = '01/01/2013' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2018' --End Value of Date Range
--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
--Extract and assign various parts of Values from Current Date to Variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE @CurrentDate < @EndDate
BEGIN
/*Begin day of week logic*/
/*Check for Change in Month of the Current date if Month changed then
Change variable value*/
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
/* Check for Change in Quarter of the Current date if Quarter changed then change
Variable value*/
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END
/* Check for Change in Year of the Current date if Year changed then change
Variable value*/
IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END
-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)
/*End day of week logic*/
/* Populate Your Dimension Table with values*/
INSERT INTO [dbo].[DimDate]
SELECT
CONVERT (char(8),@CurrentDate,112) as DateKey,
@CurrentDate AS Date,
CONVERT (char(10),@CurrentDate,103) as FullDateUK,
CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
DATEPART(DD, @CurrentDate) AS DayOfMonth,
--Apply Suffix values like 1st, 2nd 3rd etc..
CASE
WHEN DATEPART(DD,@CurrentDate) IN (11,12,13) _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3 _
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
END AS DaySuffix,
DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,
-- check for day of week as Per US and change it as per UK format
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END
AS DayOfWeekUK,
@DayOfWeekInMonth AS DayOfWeekInMonth,
@DayOfWeekInYear AS DayOfWeekInYear,
@DayOfQuarter AS DayOfQuarter,
DATEPART(DY, @CurrentDate) AS DayOfYear,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR, _
DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR, _
DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), _
@CurrentDate) / 7) + 1 AS WeekOfQuarter,
DATEPART(WW, @CurrentDate) AS WeekOfYear,
DATEPART(MM, @CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
DATEPART(QQ, @CurrentDate) AS Quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS QuarterName,
DATEPART(YEAR, @CurrentDate) AS Year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR, _
DATEPART(YY, @CurrentDate)) AS MonthYear,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) + _
CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
@CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, _
(DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1, _
@CurrentDate)))) AS LastDayOfMonth,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY, _
@CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY, _
@CurrentDate))) AS LastDayOfYear,
NULL AS IsHolidayUSA,
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS IsWeekday,
NULL AS HolidayUSA, Null, Null
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
/*******************************************************************************************/
/* Update HOLIDAY fields of UK as per Govt. Declaration of National Holiday ****/
-- Good Friday April 18
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Good Friday'
WHERE [Month] = 4 AND [DayOfMonth] = 18
-- Easter Monday April 21
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Easter Monday'
WHERE [Month] = 4 AND [DayOfMonth] = 21
-- Early May Bank Holiday May 5
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Early May Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 5
-- Spring Bank Holiday May 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Spring Bank Holiday'
WHERE [Month] = 5 AND [DayOfMonth] = 26
-- Summer Bank Holiday August 25
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Summer Bank Holiday'
WHERE [Month] = 8 AND [DayOfMonth] = 25
-- Boxing Day December 26
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Boxing Day'
WHERE [Month] = 12 AND [DayOfMonth] = 26
--CHRISTMAS
UPDATE [dbo].[DimDate]
SET HolidayUK = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
--New Years Day
UPDATE [dbo].[DimDate]
SET HolidayUK = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
--Update flag for UK Holidays 1= Holiday, 0=No Holiday
UPDATE [dbo].[DimDate]
SET IsHolidayUK = CASE WHEN HolidayUK IS NULL _
THEN 0 WHEN HolidayUK IS NOT NULL THEN 1 END
/*Update HOLIDAY Field of USA In dimension*/
/*THANKSGIVING - Fourth THURSDAY in November*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Thanksgiving Day'
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Thursday'
AND DayOfWeekInMonth = 4
/*CHRISTMAS*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Christmas Day'
WHERE [Month] = 12 AND [DayOfMonth] = 25
/*4th of July*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Independance Day'
WHERE [Month] = 7 AND [DayOfMonth] = 4
/*New Years Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'New Year''s Day'
WHERE [Month] = 1 AND [DayOfMonth] = 1
/*Memorial Day - Last Monday in May*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Memorial Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MAX(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'May'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
/*Labor Day - First Monday in September*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Labor Day'
FROM [dbo].[DimDate]
WHERE DateKey IN
(
SELECT
MIN(DateKey)
FROM [dbo].[DimDate]
WHERE
[MonthName] = 'September'
AND [DayOfWeekUSA] = 'Monday'
GROUP BY
[Year],
[Month]
)
/*Valentine's Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Valentine''s Day'
WHERE
[Month] = 2
AND [DayOfMonth] = 14
/*Saint Patrick's Day*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Saint Patrick''s Day'
WHERE
[Month] = 3
AND [DayOfMonth] = 17
/*Martin Luthor King Day - Third Monday in January starting in 1983*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Martin Luthor King Jr Day'
WHERE
[Month] = 1
AND [DayOfWeekUSA] = 'Monday'
AND [Year] >= 1983
AND DayOfWeekInMonth = 3
/*President's Day - Third Monday in February*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'President''s Day'
WHERE
[Month] = 2
AND [DayOfWeekUSA] = 'Monday'
AND DayOfWeekInMonth = 3
/*Mother's Day - Second Sunday of May*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Mother''s Day'
WHERE
[Month] = 5
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 2
/*Father's Day - Third Sunday of June*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Father''s Day'
WHERE
[Month] = 6
AND [DayOfWeekUSA] = 'Sunday'
AND DayOfWeekInMonth = 3
/*Halloween 10/31*/
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Halloween'
WHERE
[Month] = 10
AND [DayOfMonth] = 31
/*Election Day - The first Tuesday after the first Monday in November*/
BEGIN
DECLARE @Holidays TABLE (ID INT IDENTITY(1,1), _
DateID int, Week TINYINT, YEAR CHAR(4), DAY CHAR(2))
INSERT INTO @Holidays(DateID, [Year],[Day])
SELECT
DateKey,
[Year],
[DayOfMonth]
FROM [dbo].[DimDate]
WHERE
[Month] = 11
AND [DayOfWeekUSA] = 'Monday'
ORDER BY
YEAR,
DayOfMonth
DECLARE @CNTR INT, @POS INT, @STARTYEAR INT, @ENDYEAR INT, @MINDAY INT
SELECT
@CURRENTYEAR = MIN([Year])
, @STARTYEAR = MIN([Year])
, @ENDYEAR = MAX([Year])
FROM @Holidays
WHILE @CURRENTYEAR <= @ENDYEAR
BEGIN
SELECT @CNTR = COUNT([Year])
FROM @Holidays
WHERE [Year] = @CURRENTYEAR
SET @POS = 1
WHILE @POS <= @CNTR
BEGIN
SELECT @MINDAY = MIN(DAY)
FROM @Holidays
WHERE
[Year] = @CURRENTYEAR
AND [Week] IS NULL
UPDATE @Holidays
SET [Week] = @POS
WHERE
[Year] = @CURRENTYEAR
AND [Day] = @MINDAY
SELECT @POS = @POS + 1
END
SELECT @CURRENTYEAR = @CURRENTYEAR + 1
END
UPDATE [dbo].[DimDate]
SET HolidayUSA = 'Election Day'
FROM [dbo].[DimDate] DT
JOIN @Holidays HL ON (HL.DateID + 1) = DT.DateKey
WHERE
[Week] = 1
END
--set flag for USA holidays in Dimension
UPDATE [dbo].[DimDate]
SET IsHolidayUSA = CASE WHEN HolidayUSA IS NULL THEN 0 WHEN HolidayUSA IS NOT NULL THEN 1 END
/*****************************************************************************************/
2
Another suggestion:
/* SQLQUERY - MONTA LISTA COM DATA INICIAL ATÉ HOJE */
DECLARE @TMP TABLE
(
DATA DATETIME NULL
)
DECLARE @DATAINI DATETIME
SELECT @DATAINI = '2017.01.01 00:00:00.000'
INSERT INTO @TMP
SELECT @DATAINI
WHILE (SELECT MAX(DATA) FROM @TMP) <= GETDATE()
BEGIN
INSERT INTO @TMP
SELECT DATEADD(DD,1,MAX(DATA))
FROM @TMP
END
SELECT * FROM @TMP
2
The following code prints all dates in a range.
DECLARE @Data1 DATE, @Data2 DATE
SET @Data1 = '20150701'
SET @Data2 = '20150705'
SELECT CONVERT(varchar, DATEADD(DAY, number + 1, @Data1), 103) [Data]
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(DAY, number + 1, @Data1) < @Data2
I couldn’t see your fiddle, =/
@Erloncharles I fixed the link. See now.
It worked out, very good solution too, but wanted to understand a little more about it, the main doubt is what it would be type = 'P'?
@Erloncharles spt_values is one of those mysterious tables whose documentation is half Rcana. Basically, what it does in this code is to serve as an incrementer. type = P I am indicating that I want a sequence packed, that is, a numerical sequence starting at 0 and ending at 2047. Here’s an excellent explanation about her.
Very good, thank you
Excellent. I had no knowledge about the spt_values and I found very interesting the use.
0
DECLARE @dataInicio DATE
DECLARE @dataFim DATE
BEGIN
CREATE TABLE #calendario
(
data DATE,
yearmonth VARCHAR(9)
)
SET @dataInicio = ( '1900-01-01' )
SET @dataFim = ( Cast(Getdate() AS DATE) )
WHILE @dataInicio <= @dataFim
BEGIN
INSERT INTO #calendario
VALUES (@dataInicio,
(SELECT Format(@dataInicio, 'MM-yyyy')))
SET @dataInicio = Dateadd(day, 1, @dataInicio)
END
SELECT *
FROM #calendario
ORDER BY data DESC
END
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.
I have seen such a solution in a project, and it was made for performance issues. It worked (and it works) very well.
– Dherik