What is the best way to rebuild a date from integer inputs?
How about
declare @quarter int = 4
declare @year int = 2018
select datefromparts(@year,(@quarter-1)*3+1,1)
or if you're still using SQL 2008:
select dateadd(month,(@quarter-1)*3,dateadd(year, @year-2018,'20180101'))
Let me suggest to NOT use a date separator like '-' or '/', it deppends on regional settings, use YYYYMMDD
format.
declare @quarter int,
@year int,
@date date
set @quarter = 4
set @year = 2018
set @date = cast(@year as varchar(4)) + '0101'
set @date = dateadd(quarter, 1 - 1, @date)
print @date
set @date = cast(@year as varchar(4)) + '0101'
set @date = dateadd(quarter, 2 - 1, @date)
print @date
set @date = cast(@year as varchar(4)) + '0101'
set @date = dateadd(quarter, 3 - 1, @date)
print @date
set @date = cast(@year as varchar(4)) + '0101'
set @date = dateadd(quarter, 4 - 1, @date)
print @date
2018-01-01
2018-04-01
2018-07-01
2018-10-01
db<>fiddle here
You could Create a date dimension or calendar table in SQL Server and query it
--demo setup
drop table if exists #dim
DECLARE @StartDate DATE = '20000101', @NumberOfYears INT = 30;
-- prevent set or regional settings from interfering with
-- interpretation of dates / literals
SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;
DECLARE @CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);
-- this is just a holding table for intermediate calculations:
CREATE TABLE #dim
(
[date] DATE PRIMARY KEY,
[day] AS DATEPART(DAY, [date]),
[month] AS DATEPART(MONTH, [date]),
FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]), 0)),
[MonthName] AS DATENAME(MONTH, [date]),
[week] AS DATEPART(WEEK, [date]),
[ISOweek] AS DATEPART(ISO_WEEK, [date]),
[DayOfWeek] AS DATEPART(WEEKDAY, [date]),
[quarter] AS DATEPART(QUARTER, [date]),
[year] AS DATEPART(YEAR, [date]),
FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),
Style112 AS CONVERT(CHAR(8), [date], 112),
Style101 AS CONVERT(CHAR(10), [date], 101)
);
-- use the catalog views to generate as many rows as we need
INSERT #dim([date])
SELECT d
FROM
(
SELECT d = DATEADD(DAY, rn - 1, @StartDate)
FROM
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))
rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
FROM sys.all_objects AS s1
CROSS JOIN sys.all_objects AS s2
-- on my system this would support > 5 million days
ORDER BY s1.[object_id]
) AS x
) AS y;
drop table if exists dbo.DateDimension
CREATE TABLE dbo.DateDimension
(
--DateKey INT NOT NULL PRIMARY KEY,
[Date] DATE NOT NULL,
[Day] TINYINT NOT NULL,
DaySuffix CHAR(2) NOT NULL,
[Weekday] TINYINT NOT NULL,
WeekDayName VARCHAR(10) NOT NULL,
IsWeekend BIT NOT NULL,
IsHoliday BIT NOT NULL,
HolidayText VARCHAR(64) SPARSE,
DOWInMonth TINYINT NOT NULL,
[DayOfYear] SMALLINT NOT NULL,
WeekOfMonth TINYINT NOT NULL,
WeekOfYear TINYINT NOT NULL,
ISOWeekOfYear TINYINT NOT NULL,
[Month] TINYINT NOT NULL,
[MonthName] VARCHAR(10) NOT NULL,
[Quarter] TINYINT NOT NULL,
QuarterName VARCHAR(6) NOT NULL,
[Year] INT NOT NULL,
MMYYYY CHAR(6) NOT NULL,
MonthYear CHAR(7) NOT NULL,
FirstDayOfMonth DATE NOT NULL,
LastDayOfMonth DATE NOT NULL,
FirstDayOfQuarter DATE NOT NULL,
LastDayOfQuarter DATE NOT NULL,
FirstDayOfYear DATE NOT NULL,
LastDayOfYear DATE NOT NULL,
FirstDayOfNextMonth DATE NOT NULL,
FirstDayOfNextYear DATE NOT NULL
);
INSERT dbo.DateDimension WITH (TABLOCKX)
SELECT
--DateKey = CONVERT(INT, Style112),
[Date] = [date],
[Day] = CONVERT(TINYINT, [day]),
DaySuffix = CONVERT(CHAR(2), CASE WHEN [day] / 10 = 1 THEN 'th' ELSE
CASE RIGHT([day], 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd' ELSE 'th' END END),
[Weekday] = CONVERT(TINYINT, [DayOfWeek]),
[WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),
[IsWeekend] = CONVERT(BIT, CASE WHEN [DayOfWeek] IN (1,7) THEN 1 ELSE 0 END),
[IsHoliday] = CONVERT(BIT, 0),
HolidayText = CONVERT(VARCHAR(64), NULL),
[DOWInMonth] = CONVERT(TINYINT, ROW_NUMBER() OVER
(PARTITION BY FirstOfMonth, [DayOfWeek] ORDER BY [date])),
[DayOfYear] = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),
WeekOfMonth = CONVERT(TINYINT, DENSE_RANK() OVER
(PARTITION BY [year], [month] ORDER BY [week])),
WeekOfYear = CONVERT(TINYINT, [week]),
ISOWeekOfYear = CONVERT(TINYINT, ISOWeek),
[Month] = CONVERT(TINYINT, [month]),
[MonthName] = CONVERT(VARCHAR(10), [MonthName]),
[Quarter] = CONVERT(TINYINT, [quarter]),
QuarterName = CONVERT(VARCHAR(6), CASE [quarter] WHEN 1 THEN 'First'
WHEN 2 THEN 'Second' WHEN 3 THEN 'Third' WHEN 4 THEN 'Fourth' END),
[Year] = [year],
MMYYYY = CONVERT(CHAR(6), LEFT(Style101, 2) + LEFT(Style112, 4)),
MonthYear = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),
FirstDayOfMonth = FirstOfMonth,
LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),
FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),
LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),
FirstDayOfYear = FirstOfYear,
LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),
FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),
FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)
FROM #dim
OPTION (MAXDOP 1);
--solution
SELECT min(Date)
FROM [Test].[dbo].[DateDimension]
where [year] = 2018 and [Quarter]=4
| Date |
|------------|
| 2018-10-01 |