mjhjmtu

I've got a bunch of financial reports, and we want to be able to pass them two inputs (year and quarter) as variables.

I'm doing it this way, but I really don't like it:

 declare @quarter int,
 @year int,
 @date date

 set @quarter = 4
 set @year = 2018


 set @date = cast(@year as varchar(4)) + '-01-01'
 set @date = dateadd(quarter, @quarter - 1, @date)


 print @date

Question What is the best way to reconstruct a date from integer inputs?

desired result:

 2018-10-01

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 |

I would avoid using strings at all but use date arithmetic combined with a known (or even unknown!) zero epoch.

DECLARE @epoch DATE = CONVERT(DATETIME, 0);
/* for some reason SQL Server let's you cast int to datetime but not to date, the above casts via datetime (second cast implicit) */

SET @date = DATEADD(MONTH, (@quarter-1)*3, DATEADD(YEAR, @year - YEAR(@epoch), @epoch));

This avoids string to date comparison which is messy, culture-dependent and expensive.