时间戳

首页 > 解决方案 > 两个日期之间的 SQL，其中日期用整数表示

sql-server - 两个日期之间的 SQL，其中日期用整数表示

问题描述

我有一个存储过程。如果我想在两个日期时间之间获取行，我会使用类似的东西：

SELECT * 
FROM Orders
WHERE OrderDate BETWEEN @dateTimeFrom AND @dateTimeTo;

当您在数据库中的日期不是日期时间而是整数时，您如何做到这一点。

我尝试了这样的条件：

WHERE 
    @fromYear <= GS.[Year] AND GS.[Year] <= @toYear 
    AND @fromMonth <= GS.[Month] AND GS.[Month] <= @toMonth 
    AND @fromDay <= GS.[Day] AND GS.[Day] <= @toDay

没有按我的预期工作。

我也试过这个：

AND DATEFROMPARTS(GS.[Year], GS.[Month], GS.[Day]) BETWEEN CAST(@from AS date) AND CAST(@to AS date)

编辑：完整程序：

ALTER PROCEDURE [dbo].[Stats] 
    @from VARCHAR(15) = NULL,
    @to VARCHAR(15) = NULL
AS 
BEGIN 
    SET nocount ON;

    DECLARE @fromYear INT = NULL;
    SET @fromYear = CASE WHEN @from IS NOT NULL THEN Datepart(year, @from) END

    SELECT @fromYear

    DECLARE @fromMonth INT = NULL;
    SET @fromMonth = CASE WHEN @from IS NOT NULL THEN Datepart(month, @from) END
    SELECT @fromMonth

    DECLARE @fromDay INT = NULL;
    SET @fromDay = CASE WHEN @from IS NOT NULL THEN Datepart(day, @from) END

    SELECT @fromDay

    DECLARE @toYear INT = NULL;
    SET @toYear = CASE WHEN @to IS NOT NULL THEN Datepart(year, @to) ELSE @fromYear END

    SELECT @toYear

    DECLARE @toMonth INT = NULL;
    SET @toMonth = CASE WHEN @to IS NOT NULL THEN Datepart(month, @to) ELSE @fromMonth END

    SELECT @toMonth

    DECLARE @toDay INT = NULL;
    SET @toDay = CASE WHEN @to IS NOT NULL THEN Datepart(day, @to) ELSE @fromDay END

    SELECT @toDay

    SELECT 
        GS.[name]
    FROM   
        [dbo].[gamestatsdaily] AS GS 
    WHERE   
        (@from IS NULL OR (@fromYear <= GS.[Year] AND GS.[Year] <= @toYear 
                           AND @fromMonth <= GS.[Month] AND GS.[Month] <= @toMonth 
                           AND @fromDay <= GS.[Day] AND GS.[Day] <= @toDay))
    ORDER BY 
        GS.[year] ASC, GS.[month] ASC, GS.[day] ASC 
END

数据示例：

Name        Day   Month     Year    
----------------------------------
Microsoft   24      5       2018    
Apple       12      7       2018    
Thor        13      8       2018

标签： sql-server

解决方案

正如我在评论中所说，这里真正的答案是修复您的数据定义。答案包括同时使用DATEFROMPARTS“简单”答案，但是也使用“坏”答案；因为查询将是非 SARGable。但是，它也有一个答案，将列的数据类型更改为int它们应该是的，然后将PERSISTED计算列添加到表中，可以查询：

USE Sandbox;
GO
CREATE TABLE SomeTable (ID int IDENTITY(1,1),
                        TheYear varchar(15), --Should be an int
                        TheMonth varchar(15), --Should be an int
                        TheDay varchar(15)); --Should be an int

INSERT INTO dbo.SomeTable (TheYear,
                           TheMonth,
                           TheDay)
VALUES('2018','06','01'),
      ('2018','06','05'),
      ('2018','06','15'),
      ('2018','06','20'),
      ('2018','06','22'),
      ('2018','06','23'),
      ('2018','07','01'),
      ('2018','07','03');
GO

DECLARE @StartDate date, @EndDate date;

SET @StartDate = '20180620';
SET @EndDate = '20180630';
--Non SARGable answer:
SELECT *
FROM dbo.SomeTable
--A load of TRY_CONVERT because you've chosen a varchar to store an int. Thus your data can't be trusted to contain an int.
WHERE DATEFROMPARTS(TRY_CONVERT(int,TheYear),TRY_CONVERT(int,TheMonth),TRY_CONVERT(int,TheDay)) BETWEEN @StartDate AND @EndDate;
GO
--The real answer:
--Firstly, let's fix those data types:
ALTER TABLE dbo.SomeTable ADD TheYearI int;
ALTER TABLE dbo.SomeTable ADD TheMonthI int;
ALTER TABLE dbo.SomeTable ADD TheDayI int;
--And update the values
GO
UPDATE dbo.SomeTable
SET TheYearI = TRY_CONVERT(int,TheYear),
    TheMonthI = TRY_CONVERT(int,TheMonth),
    TheDayI = TRY_CONVERT(int,TheDay);
GO
--Drop the old columns
ALTER TABLE dbo.SomeTable DROP COLUMN TheYear;
ALTER TABLE dbo.SomeTable DROP COLUMN TheMonth;
ALTER TABLE dbo.SomeTable DROP COLUMN TheDay;
GO
--Rename the new columns
EXEC sp_rename 'dbo.SomeTable.TheYearI','TheYear','COLUMN';
EXEC sp_rename 'dbo.SomeTable.TheMonthI','TheMonth','COLUMN';
EXEC sp_rename 'dbo.SomeTable.TheDayI','TheDay','COLUMN';
GO
--Creat the new computed column

ALTER TABLE dbo.SomeTable ADD TheDate AS DATEFROMPARTS(TheYear, TheMonth, TheDay) PERSISTED;
GO
--And now the simple query:
DECLARE @StartDate date, @EndDate date;

SET @StartDate = '20180620';
SET @EndDate = '20180630';

SELECT *
FROM dbo.SomeTable
WHERE TheDate BETWEEN @StartDate AND @EndDate;

GO

DROP TABLE dbo.SomeTable;
GO

推荐阅读