主页 / dba / 问题 / 226838
Accepted
James
Asked: 2019-01-11 13:39:06 +0800 CST

从整数输入重建日期的最佳方法是什么？

  • 772

我有一堆财务报告，我们希望能够将两个输入（年和季度）作为变量传递给它们。

我这样做，但我真的不喜欢它：

    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

问题从整数输入重建日期的最佳方法是什么？

期望的结果：

    2018-10-01
sql-server-2008-r2

4 个回答

  1. 怎么样

    declare @quarter int = 4
declare @year int = 2018

select datefromparts(@year,(@quarter-1)*3+1,1)

    或者如果您仍在使用 SQL 2008：

    select dateadd(month,(@quarter-1)*3,dateadd(year, @year-2018,'20180101'))
    • 12

  2. 我建议不要使用像“-”或“/”这样的日期分隔符，它取决于区域设置，使用YYYYMMDD格式。

    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<>在这里摆弄

    • 10
  3. Best Answer

    您可以在 SQL Server 中创建日期维度或日历表并查询它

    --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 |
    • 5

  4. 我会完全避免使用字符串，而是将日期算术与已知（甚至未知！）零纪元结合使用。

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

    这避免了字符串到日期的比较，这种比较是混乱的、依赖于文化的和昂贵的。

    • 4

相关问题