By James


2019-01-10 21:39:06 8 Comments

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

4 comments

@IanF1 2019-01-11 06:53:24

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.

@Scott Hodgin 2019-01-10 21:51:45

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 |

@James 2019-01-11 15:41:22

This is the right way to do it - this way we can index the date parts, so it will be a bazillion times faster to join to DateDimension than to put a function on a column in the where clause.

@David Browne - Microsoft 2019-01-10 21:49:21

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'))

@McNets 2019-01-10 21:45:18

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

@a_horse_with_no_name 2019-01-11 07:03:22

My understanding was, that '2018-10-01' is independent of regional settings and will always be parsed correctly by SQL Server. Isn't that true?

@McNets 2019-01-11 08:24:17

@a_horse_with_no_name it should due it is ISO 8601, but at least in my company where we use a mixed-language installation of SQL servers (English, Spanish) I ended using the less unambiguous format YYYYMMDD HH:MM:SS to avoid date conversion issues.

@Martin Smith 2019-01-11 11:17:56

@a_horse_with_no_name - This is correct for the newer temporal datatypes (e.g. date and datetime2) but not for the legacy datetime type. So as the questioner is casting to date the format is fine in this case

@a_horse_with_no_name 2019-01-11 11:42:32

@MartinSmith: thanks for the clarification

@IanF1 2019-01-19 18:47:50

"2001-08-12" isn't always interpreted in the way you would expect. I never worked out how but in our development environment it would interpret it as yyyy-dd-mm, and throw an out of range error for "2001-08-13". I try to avoid string-to-date conversion at all, and where it is necessary I make sure to use a month name - "12 aug 2001" is unambiguous.

Related Questions

Sponsored Content

2 Answered Questions

1 Answered Questions

[SOLVED] Rebuild the Fulltext catalog

1 Answered Questions

[SOLVED] SQL Server Maintenance Plan Fails to Rebuild the Indexes

  • 2012-02-15 10:08:03
  • Maher Abu Zer
  • 7579 View
  • 4 Score
  • 1 Answer
  • Tags:   sql-server-2008-r2

1 Answered Questions

[SOLVED] Get the marketdata based on present date and previous date

2 Answered Questions

2 Answered Questions

2 Answered Questions

1 Answered Questions

2 Answered Questions

[SOLVED] The best way to rename the tables after development completed

2 Answered Questions

[SOLVED] What is the best way to mirror an ASPState database?

Sponsored Content