How to create a Calendar table for 100 years in Sql
Here is a generic script you can use in SQL server. just amend the start and end dates:
IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Calendar]
END
CREATE TABLE [Calendar]
(
[CalendarDate] DATETIME
)
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = GETDATE()
SET @EndDate = DATEADD(d, 365, @StartDate)
WHILE @StartDate <= @EndDate
BEGIN
INSERT INTO [Calendar]
(
CalendarDate
)
SELECT
@StartDate
SET @StartDate = DATEADD(dd, 1, @StartDate)
END
If you want a more advanced calendar here is one I found on the net a while ago:
CREATE SCHEMA Auxiliary
-- We put our auxiliary tables and stuff in a separate schema
-- One of the great new things in SQL Server 2005
go
CREATE FUNCTION Auxiliary.Computus
-- Computus (Latin for computation) is the calculation of the date of
-- Easter in the Christian calendar
-- http://en.wikipedia.org/wiki/Computus
-- I'm using the Meeus/Jones/Butcher Gregorian algorithm
(
@Y INT -- The year we are calculating easter sunday for
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@a INT,
@b INT,
@c INT,
@d INT,
@e INT,
@f INT,
@g INT,
@h INT,
@i INT,
@k INT,
@L INT,
@m INT
SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b - @f + 1) / 3
SET @h = (19 * @a + @b - @d - @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END
GO
CREATE TABLE [Auxiliary].[Calendar] (
-- This is the calendar table
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO
ALTER TABLE [Auxiliary].[Calendar]
-- In Celkoish style I'm manic about constraints (Never use em ;-))
-- http://www.celko.com/
ADD CONSTRAINT [Calendar_ck] CHECK ( ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week] BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN ('HOLIDAY', 'SATURDAY', 'SUNDAY', 'BANKDAY')))
GO
SET DATEFIRST 1;
-- I want my table to contain datedata acording to ISO 8601
-- http://en.wikipedia.org/wiki/ISO_8601
-- thus first day of a week is monday
WITH Dates(Date)
-- A recursive CTE that produce all dates between 1999 and 2020-12-31
AS
(
SELECT cast('1999' AS DateTime) Date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01
UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast('2021' AS DateTime) -1
),
DatesAndThursdayInWeek(Date, Thursday)
-- The weeks can be found by counting the thursdays in a year so we find
-- the thursday in the week for a particular date
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date - 1
WHEN 6 THEN Date - 2
WHEN 7 THEN Date - 3
END AS Thursday
FROM Dates
),
Weeks(Week, Thursday)
-- Now we produce the weeknumers for the thursdays
-- ROW_NUMBER is new to SQL Server 2005
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week, Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,
-- Fiscal year may be different to the actual year in Norway the are the same
-- http://en.wikipedia.org/wiki/Fiscal_year
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,
CASE
-- Holidays in Norway
-- For other countries and states: Wikipedia - List of holidays by country
-- http://en.wikipedia.org/wiki/List_of_holidays_by_country
WHEN (DATEPART(DayOfYear, d.Date) = 1) -- New Year's Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))-7) -- Palm Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-3) -- Maundy Thursday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-2) -- Good Friday
OR (d.Date = Auxiliary.Computus(YEAR(Date))) -- Easter Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) -- Ascension Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) -- Pentecost
OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) -- Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) -- Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) -- Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) -- Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) -- Boxing day
THEN 'HOLIDAY'
WHEN DATEPART(Weekday, d.Date) = 6 THEN 'SATURDAY'
WHEN DATEPART(Weekday, d.Date) = 7 THEN 'SUNDAY'
ELSE 'BANKDAY'
END KindOfDay,
CASE
-- Description of holidays in Norway
WHEN (DATEPART(DayOfYear, d.Date) = 1) THEN 'New Year''s Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7) THEN 'Palm Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3) THEN 'Maundy Thursday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2) THEN 'Good Friday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))) THEN 'Easter Sunday'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN 'Ascension Day'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN 'Pentecost'
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN 'Whitmonday'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1) THEN 'Labour day'
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17) THEN 'Constitution day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25) THEN 'Cristmas day'
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26) THEN 'Boxing day'
END Description
FROM DatesAndThursdayInWeek d
-- This join is for getting the week into the result set
inner join Weeks w
on d.Thursday = w.Thursday
OPTION(MAXRECURSION 0)
GO
CREATE FUNCTION Auxiliary.Numbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS @RetNumbers TABLE
(
[Number] int PRIMARY KEY NOT NULL
)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END
GO
CREATE FUNCTION Auxiliary.iNumbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS TABLE
AS
RETURN(
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
SELECT n AS Number from Numbers
)
GO
How to generate calendar table having begin month date and end month Date
If you don't have a calendar table, you can use an ad-hoc tally table
Example
Declare @Date1 date = '2018-01-01'
Declare @Date2 date = GetDate()
Select [Month] = D
,[Eomonth] = case when EOMONTH(D)>@Date2 then convert(date,GetDate()) else EOMONTH(D) end
From (
Select Top (DateDiff(Month,@Date1,@Date2)+1)
D=DateAdd(Month,-1+Row_Number() Over (Order By (Select Null)),@Date1)
From master..spt_values n1
) A
Returns
Month Eomonth
2018-01-01 2018-01-31
2018-02-01 2018-02-28
2018-03-01 2018-03-31
2018-04-01 2018-04-30
2018-05-01 2018-05-31
2018-06-01 2018-06-30
2018-07-01 2018-07-31
2018-08-01 2018-08-31
2018-09-01 2018-09-30
2018-10-01 2018-10-31
2018-11-01 2018-11-30
2018-12-01 2018-12-31
2019-01-01 2019-01-31
2019-02-01 2019-02-13 <-- Today's date
Create a calendar database table like this
If you are running MySQL 8.0, you can use a recursive query:
with recursive cte as (
select '2019-01-01 00:00:00' dt
union all
select dt + interval 1 hour from cte where dt < '2020-01-01' - interval 1 hour
)
select
row_number() over(order by dt) id,
date(dt) day,
time(dt) start_hour,
time(dt + interval 1 hour) end_hour
from cte
The recursive cte generates a list of datetimes between the given boundaries (here, that's year 2019), with a 1 hour increment. Then, the outer query produces the expected result, by extracting the day and hour parts.
You can adjust the boudaries and the increment as per your exact requirements.
Side note: I would suggest to retain the full datetime in the calendar table as well; there are many situation where having a proper datetime value is more convenient than separated dates and times.
Demo on DB Fiddle for the first day only:
id | day | start_hour | end_hour
-: | :--------- | :-------------- | :--------------
1 | 2019-01-01 | 00:00:00.000000 | 01:00:00.000000
2 | 2019-01-01 | 01:00:00.000000 | 02:00:00.000000
3 | 2019-01-01 | 02:00:00.000000 | 03:00:00.000000
4 | 2019-01-01 | 03:00:00.000000 | 04:00:00.000000
5 | 2019-01-01 | 04:00:00.000000 | 05:00:00.000000
6 | 2019-01-01 | 05:00:00.000000 | 06:00:00.000000
7 | 2019-01-01 | 06:00:00.000000 | 07:00:00.000000
8 | 2019-01-01 | 07:00:00.000000 | 08:00:00.000000
9 | 2019-01-01 | 08:00:00.000000 | 09:00:00.000000
10 | 2019-01-01 | 09:00:00.000000 | 10:00:00.000000
11 | 2019-01-01 | 10:00:00.000000 | 11:00:00.000000
12 | 2019-01-01 | 11:00:00.000000 | 12:00:00.000000
13 | 2019-01-01 | 12:00:00.000000 | 13:00:00.000000
14 | 2019-01-01 | 13:00:00.000000 | 14:00:00.000000
15 | 2019-01-01 | 14:00:00.000000 | 15:00:00.000000
16 | 2019-01-01 | 15:00:00.000000 | 16:00:00.000000
17 | 2019-01-01 | 16:00:00.000000 | 17:00:00.000000
18 | 2019-01-01 | 17:00:00.000000 | 18:00:00.000000
19 | 2019-01-01 | 18:00:00.000000 | 19:00:00.000000
20 | 2019-01-01 | 19:00:00.000000 | 20:00:00.000000
21 | 2019-01-01 | 20:00:00.000000 | 21:00:00.000000
22 | 2019-01-01 | 21:00:00.000000 | 22:00:00.000000
23 | 2019-01-01 | 22:00:00.000000 | 23:00:00.000000
24 | 2019-01-01 | 23:00:00.000000 | 00:00:00.000000
In earlier versions, you would typically create a large table of numbers by cross-joining subqueries, and use the number range to increment the initial date. row_number() can be emulated with a MySQL variable:
select
@id:=@id + 1 id,
date(dt) day,
time(dt) start_hour,
time(dt + interval 1 hour) end_hour,
0 prenoted
from (
select '2019-01-01' + interval d0.n + 10 * d1.n + 100 * d2.n + 1000 * d3.n hour dt
from
(
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d0
cross join (
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d1
cross join (
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d2
cross join (
select 0 n union all select 1 union all select 2 union all select 3 union all select 4
union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
) d3
where '2019-01-01' + interval d0.n + 10 * d1.n + 100 * d2.n + 1000 * d3.n hour < '2020-01-01'
) t
cross join (select @id := 0 id) i
order by dt
The above query gives you a maximum span of 10 000 hours (wich represent a little more than 416 days); you can add another cross join and update the arithmetic to handle up to 100 000 hours (and so on).
Demo on DB Fiddle for the first 24 hours.
How can I generate a temporary table filled with dates in SQL Server 2000?
This will quickly populate a table with 170 years worth of dates.
CREATE TABLE CalendarMonths (
date DATETIME,
PRIMARY KEY (date)
)
DECLARE
@basedate DATETIME,
@offset INT
SELECT
@basedate = '01 Jan 2000',
@offset = 1
WHILE (@offset < 2048)
BEGIN
INSERT INTO CalendarMonths SELECT DATEADD(MONTH, @offset, date) FROM CalendarMonths
SELECT @offset = @offset + @offset
END
You can then use it by LEFT joining on to that table, for the range of dates you require.
Create a DB2 Calendar table for 20 years with columns dependant on the original date
You can do this with a DB2 recursive query and date functions:
Consider:
with cte (
calendar_date,
calendar_year,
calendar_month_number,
calendar_month_name,
calendar_day_of_month,
calendar_day_of_week,
calendar_day_name
) as (
select
calendar_date,
year(calendar_date),
month(calendar_date),
monthname(calendar_date),
dayofmonth(calendar_date),
dayofweek(calendar_date),
dayname(calendar_date)
from (values(date('2000-01-01'))) as t(calendar_date)
union all
select
calendar_date + 1,
year(calendar_date + 1),
month(calendar_date + 1),
monthname(calendar_date + 1),
dayofmonth(calendar_date + 1),
dayofweek(calendar_date + 1),
dayname(calendar_date + 1)
from cte where calendar_date < date('2021-01-01')
)
select * from cte
Note: it is unclear to me what column CALENDAR_YEAR_MONTH means, so I left it apart.
Demo on DB Fiddle for the first 10 days:
CALENDAR_DATE | CALENDAR_YEAR | CALENDAR_MONTH_NUMBER | CALENDAR_MONTH_NAME | CALENDAR_DAY_OF_MONTH | CALENDAR_DAY_OF_WEEK | CALENDAR_DAY_NAME
------------: | ------------: | --------------------: | ------------------: | --------------------: | -------------------: | ----------------:
2000-01-01 | 2000 | 1 | January | 1 | 7 | Saturday
2000-01-02 | 2000 | 1 | January | 2 | 1 | Sunday
2000-01-03 | 2000 | 1 | January | 3 | 2 | Monday
2000-01-04 | 2000 | 1 | January | 4 | 3 | Tuesday
2000-01-05 | 2000 | 1 | January | 5 | 4 | Wednesday
2000-01-06 | 2000 | 1 | January | 6 | 5 | Thursday
2000-01-07 | 2000 | 1 | January | 7 | 6 | Friday
2000-01-08 | 2000 | 1 | January | 8 | 7 | Saturday
2000-01-09 | 2000 | 1 | January | 9 | 1 | Sunday
2000-01-10 | 2000 | 1 | January | 10 | 2 | Monday
Create calendar table of next year using stored procedure in MySQL
You could split up the string in the concat function and have the variable outside of the string like you did in the insert clause. It would look like this:
SET @s = CONCAT('INSERT INTO tbl_calendar_', @tbl, ' SELECT
cal.date AS cdate,
DAY (cal.date) AS cday,
MONTH (cal.date) AS cmonth,
YEAR (cal.date) AS cyear,
NULL
FROM
(
SELECT \'',
@tbl, '-01-01\' + INTERVAL d.i * 1000 + c.i * 100 + a.i * 10 + b.i DAY AS date
...'
You will need to split up the string in the concat function like this every time that you use the @tbl variable in the query.
create calendar table and join it to my table - lost as can be
@Ted Basically what you have to do is create a derived table that you can LEFT JOIN your existing table to so every day in the month will be listed and you can display data from your table for the days where it is present. This query is copy/paste ready, all you need to do is change the references to my_table to be the table name you want to get the data from.
Here's the example query:
SELECT `dateList`.`Date` AS `Date`,
CONCAT(YEAR(`dateList`.`Date`),'/',DAY(`dateList`.`Date`)) AS `year_month`,
YEAR(`dateList`.`Date`) AS `year`,
DAY(`dateList`.`Date`) AS `day`,
'Bob Smith' AS `name`,
CASE WHEN `mt`.`date` IS NULL THEN 0
ELSE COUNT(`mt`.`id`)
END AS `amt`
FROM
-- ---------------------------------------------------------------------------------------------------------------------------
-- this is the part you can copy/paste to be used to left join in a table of your choice ----------------------------------
(
SELECT `a`.`Date`
FROM (
SELECT LAST_DAY('2020-08-01') - INTERVAL (`a`.`a` + (10 * `b`.`a`) + (100 * `c`.`a`)) DAY AS `Date`
FROM (SELECT 0 AS `a` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS `a`
CROSS JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS `b`
CROSS JOIN (SELECT 0 AS `a` UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS `c`
) AS `a`
WHERE `a`.`Date` BETWEEN '2020-08-01' AND LAST_DAY('2020-08-01')
) AS `dateList`
-- ---------------------------------------------------------------------------------------------------------------------------
LEFT JOIN `my_table` AS `mt` ON `dateList`.`Date` = DATE(`mt`.`date`)
AND `mt`.`name` = 'Bob Smith'
GROUP BY `dateList`.`Date`
ORDER BY `dateList`.`Date` ASC
Here is a working example in sql fiddle.
Let me know if this doesn't make sense or if you need something different.
How to populate a table with a range of dates?
Try this:
DROP PROCEDURE IF EXISTS filldates;
DELIMITER |
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
WHILE dateStart <= dateEnd DO
INSERT INTO tablename (_date) VALUES (dateStart);
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;
|
DELIMITER ;
CALL filldates('2011-01-01','2011-12-31');
Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/65d13/1
EDIT (to check if date already exists) as asked by Andrew Fox.
CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)
BEGIN
DECLARE adate date;
WHILE dateStart <= dateEnd DO
SET adate = (SELECT mydate FROM MyDates WHERE mydate = dateStart);
IF adate IS NULL THEN BEGIN
INSERT INTO MyDates (mydate) VALUES (dateStart);
END; END IF;
SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
END WHILE;
END;//
Here's the SQL Fiddle to play with it: http://sqlfiddle.com/#!2/66f86/1
Get all dates for all date ranges in table using SQL Server
If you are regularly dealing with "jobs" and "schedules" then I propose that you need a permanent calendar table (a table where each row is a unique date). You can create rows for dates dynamically but why do this many times when you can do it once and just re-use?
A calendar table, even of several decades, isn't "big" and when indexed they can be very fast as well. You can also store information about holidays and/or fiscal periods etc.
There are many scripts available to produce these tables, here's an answer with 2 scripts on this site: https://stackoverflow.com/a/5635628/2067753
Assuming you use the second (more comprehensive) script, then you can exclude weekends, or other conditions such as holidays, from query results.
Once you have a permanent Calendar table this style of query may be used:
CREATE TABLE WorkSchedules(
Id INTEGER NOT NULL PRIMARY KEY
,[From] DATE NOT NULL
,[To] DATE NOT NULL
);
INSERT INTO WorkSchedules(Id,[From],[To]) VALUES (1,'2018-01-01','2018-01-05');
INSERT INTO WorkSchedules(Id,[From],[To]) VALUES (2,'2018-01-12','2018-01-12');
with range as (
select min(ws.[From]) as dt_from, max(ws.[To]) dt_to
from WorkSchedules as ws
)
select c.*
from calendar as c
inner join range on c.date between range.dt_from and range.dt_to
where c.KindOfDay = 'BANKDAY'
order by c.date
and the result looks like this (note: "News Years Day" has been excluded)
Date Year Quarter Month Week Day DayOfYear Weekday Fiscal_Year Fiscal_Quarter Fiscal_Month KindOfDay Description
---- --------------------- ------ --------- ------- ------ ----- ----------- --------- ------------- ---------------- -------------- ----------- -------------
1 02.01.2018 00:00:00 2018 1 1 1 2 2 2 2018 1 1 BANKDAY NULL
2 03.01.2018 00:00:00 2018 1 1 1 3 3 3 2018 1 1 BANKDAY NULL
3 04.01.2018 00:00:00 2018 1 1 1 4 4 4 2018 1 1 BANKDAY NULL
4 05.01.2018 00:00:00 2018 1 1 1 5 5 5 2018 1 1 BANKDAY NULL
5 08.01.2018 00:00:00 2018 1 1 2 8 8 1 2018 1 1 BANKDAY NULL
6 09.01.2018 00:00:00 2018 1 1 2 9 9 2 2018 1 1 BANKDAY NULL
7 10.01.2018 00:00:00 2018 1 1 2 10 10 3 2018 1 1 BANKDAY NULL
8 11.01.2018 00:00:00 2018 1 1 2 11 11 4 2018 1 1 BANKDAY NULL
9 12.01.2018 00:00:00 2018 1 1 2 12 12 5 2018 1 1 BANKDAY NULL
Without the where clause the full range is:
Date Year Quarter Month Week Day DayOfYear Weekday Fiscal_Year Fiscal_Quarter Fiscal_Month KindOfDay Description
---- --------------------- ------ --------- ------- ------ ----- ----------- --------- ------------- ---------------- -------------- ----------- ----------------
1 01.01.2018 00:00:00 2018 1 1 1 1 1 1 2018 1 1 HOLIDAY New Year's Day
2 02.01.2018 00:00:00 2018 1 1 1 2 2 2 2018 1 1 BANKDAY NULL
3 03.01.2018 00:00:00 2018 1 1 1 3 3 3 2018 1 1 BANKDAY NULL
4 04.01.2018 00:00:00 2018 1 1 1 4 4 4 2018 1 1 BANKDAY NULL
5 05.01.2018 00:00:00 2018 1 1 1 5 5 5 2018 1 1 BANKDAY NULL
6 06.01.2018 00:00:00 2018 1 1 1 6 6 6 2018 1 1 SATURDAY NULL
7 07.01.2018 00:00:00 2018 1 1 1 7 7 7 2018 1 1 SUNDAY NULL
8 08.01.2018 00:00:00 2018 1 1 2 8 8 1 2018 1 1 BANKDAY NULL
9 09.01.2018 00:00:00 2018 1 1 2 9 9 2 2018 1 1 BANKDAY NULL
10 10.01.2018 00:00:00 2018 1 1 2 10 10 3 2018 1 1 BANKDAY NULL
11 11.01.2018 00:00:00 2018 1 1 2 11 11 4 2018 1 1 BANKDAY NULL
12 12.01.2018 00:00:00 2018 1 1 2 12 12 5 2018 1 1 BANKDAY NULL
and weekends and holidays may be excluded using the column KindOfDay
See this as a demonstration (with build of calendar table) here: http://rextester.com/CTSW63441
Related Topics
Dplyr Left_Join by Less Than, Greater Than Condition
How to Obtain a Query Execution Plan in SQL Server
Null Values Inside Not in Clause
How to Implement a Many-To-Many Relationship in Postgresql
Is There a Combination of "Like" and "In" in Sql
SQL Left Join VS Multiple Tables on from Line
Error 1452: Cannot Add or Update a Child Row: a Foreign Key Constraint Fails
Difference Between Oracle'S Plus (+) Notation and Ansi Join Notation
Update Table Values from Another Table With the Same User Name
Remove Trailing Zeros from Decimal in SQL Server
Pass R Variable to Rodbc'S Sqlquery
Remove Duplicate Rows in MySQL
What Is the Reason Not to Use Select *
Which Is Faster/Best? Select * or Select Column1, Colum2, Column3, etc
Oracle SQL Query: Retrieve Latest Values Per Group Based on Time
Entity Attribute Value Database Vs. Strict Relational Model Ecommerce
Passing Multiple Values For a Single Parameter in Reporting Services