在SQL Server 2008中创建月份和年份的日期

The table has Month and year as separate columns, I am tying to create a function that will return the date in DD-MM-YYYY format.

该表将Month和year作为单独的列，我想创建一个以DD-MM-YYYY格式返回日期的函数。

I need to create a starting date and ending date for each combination, unfortunately the schema cannot be changed and so I have work with it.

我需要为每个组合创建一个开始日期和结束日期，遗憾的是架构无法更改，因此我可以使用它。

I have pasted the code:

我已粘贴代码：

CREATE FUNCTION fn.GetDateFromMonthYear
    (@isStartDate BIT,
     @month INT,
     @year INT)
RETURNS DATE
AS 
BEGIN
    DECLARE @finalDate AS DATE

    IF (isStartDate)
    BEGIN
        SELECT @finalDate = CAST('01' + '-' + @month + @year AS DATE)
    END
    ELSE
    BEGIN
        SELECT @finalDate = CAST(CAST('01' + '-' + CAST(CAST(@month AS INT) + 1 AS VARCHAR(20)) + @year AS DATETIME) - 1 AS DATE
    END

    RETURN @startDate
END 

There are two things how is the performance of this function can there be any improvement.

有两件事情，这个功能的性能如何有所改善。

How do we handle the leap year condition.

我们如何处理闰年条件。

Sample input and output

样本输入和输出

• year = 2017
• 年= 2017年
• month = 11
• 月= 11
• isStartDate = true
• isStartDate = true

Output: 01-11-2011

输出：01-11-2011

• year= 2017
• 年= 2017年
• month = 11
• 月= 11
• isStartDate = false
• isStartDate = false

Output: 30-11-2011

产量：30-11-2011

• year= 2017
• 年= 2017年
• month = 12
• 月= 12
• isStartDate = false
• isStartDate = false

Output: 31-12-2011

产出：31-12-2011

5 个解决方案

#1

I would use an inline table valued function instead of a scalar function. They are more flexible and better for performance. Also, date do NOT have a format. The format is used in the front end. The ANSI approved format for a date representation is YYYYMMDD. That will work no matter what your local settings are.

我会使用内联表值函数而不是标量函数。它们更灵活，性能更好。此外，日期没有格式。格式用于前端。 ANSI批准的日期表示格式为YYYYMMDD。无论您的本地设置是什么，这都将有效。

Here is a fully functional example of doing this with an inline table valued function.

这是一个使用内联表值函数执行此操作的完整功能示例。

create function GetDateFromMonthYear
(
    @month int
    , @year int
    , @isStartDate bit
) returns table as return

select MyDate = case @isStartDate 
when 1 
    then dateadd(month, datediff(month, 0, CONVERT(char(4), @year) + right('0' + CONVERT(varchar(2), @month), 2) + '01'), 0)
    else dateadd(day, -1, dateadd(month, datediff(month, 0, CONVERT(char(4), @year) + right('0' + CONVERT(varchar(2), @month), 2) + '01') + 1, 0)) 
end

GO

declare @SomeDates table
(
    MyYear int
    , MyMonth int
    , IsStartDate bit
)

insert @SomeDates
(
    MyYear
    , MyMonth
    , IsStartDate
) values
(2017, 11, 1)
,(2017, 11, 0)
,(2017, 12, 1)
,(2017, 12, 0)

select *
from @SomeDates s
cross apply dbo.GetDateFromMonthYear(s.MyMonth, s.MyYear, s.isStartDate) x

#2

A little maths trick with dates can solve this one:

日期的一些小数学技巧可以解决这个问题：

select dateadd(month, (YEARCOLUMN-1900)*12 + MONTHCOLUMN - ISSTARTDATE), ISSTARTDATE - 1)

dates are represented numerically in SQLS, with 0 being 1900-01-01 00:00:00 and every integer increment being one day after

日期在SQLS中用数字表示，0表示1900-01-01 00:00:00，每个整数增量表示后一天

Subbing 1900 off your year, then multiplying by 12 gives the number of months we have to DATEADD to a start date of 0 (the first of Jan 1900) to get to your year.

将你的年份减去1900，然后乘以12给出了我们对DATEADD到开始日期0（1900年1月1日）的月数，以达到你的年份。

Then we add one less than your month (your month is 11, we add 10 months to January to get to the start of month 11) if it's a start date, or we add the months num if it's an end date (i.e. We add another month on, we will come back a day using another trick later).

然后我们添加一个少于你的月份（你的月份是11，我们将1个月增加10个月来到11月的开始），如果它是开始日期，或者我们添加月份数量，如果它是结束日期（即我们添加再过一个月，我们将在晚些时候再使用另一个技巧回来。

Helpfully isstartdate is a 1 when it's a start date and 0 when it's an end date, so we essentially add (monthnum - isstartdate) to vary whether it's a start or an end. Note that at the moment this algorithm will produce 2017-12-01 for components of 2017, 11 and 0 (i.e. Is an end date) so we need to come back one day in this case, so...

有启发性的isstartdate在开始日期时是1，在结束日期时是0，所以我们基本上添加（monthnum - isstartdate）来改变它是开始还是结束。请注意，此算法将生成2017-12-01的2017,11和0的组件（即结束日期），因此我们需要在这种情况下返回一天，所以...

The only other bit of math we have to do is start counting from day 0 if it's a start date or -1 if it's an end date. This is easily achieved by subtracting 1 from isstartdate (meaning when it's a start date we begin counting from 0, when it's an end date we begin counting from -1. In other words, when it's an end date, our months are added to 1899-12-31)

我们要做的另一个数学运算是从第0天开始计算，如果它是开始日期，或者如果它是结束日期则为-1。这可以通过从isstartdate中减去1来轻松实现（这意味着它是一个开始日期，我们从0开始计算，当它是从-1开始计算的结束日期。换句话说，当它是结束日期时，我们的月份将被添加到1899年-12-31）

http://www.sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/14236

http://www.sqlfiddle.com/#!6/9eecb7db59d16c80417c72d1e1f4fbf1/14236

Note: I've done this sql style with "YEARCOLUMN" etc placeholders; you'd just substitute in your relevant variable names for everything in the query above, that is IN CAPS

注意：我用“YEARCOLUMN”等占位符完成了这个sql风格;您只需在相关变量名中替换上面查询中的所有内容，即IN CAPS

#3

I know that you tagged your question with 'sql-server-2008', but for anyone stumbling on this later, the EOMONTH() and DATEFROMPARTS() function was added in SQL Server 2012. Both of which make your problem a lot easier to solve.

我知道您使用'sql-server-2008'标记了您的问题，但对于后来遇到这种情况的人来说，SQL Server 2012中添加了EOMONTH（）和DATEFROMPARTS（）函数。这两个问题都使您的问题变得更加容易解决。

CREATE FUNCTION GetDateFromMonthYear
(
    @month INT
    , @year INT
    , @isStartDate BIT
) RETURNS TABLE AS RETURN
SELECT MyDate = CASE @isStartDate 
    WHEN 1 
        THEN DATEFROMPARTS(@year, @month, 1)
        ELSE EOMONTH(DATEFROMPARTS(@year, @month, 1))
    END

#4

Here is how I would write it but idea is basically the same:

这是我写它的方式，但想法基本相同：

create function dbo.GetDateFromMonthYear(
    @isStartDate bit,
    @month int,
    @year int)
returns date
as
begin
return (
    case when @isStartDate = 1 
    then convert(date, cast(@year as varchar(max)) + '-' + cast(@month as varchar(max)) + '-1', 121)
    else dateadd(day, -1, dateadd(month, 1, convert(date, cast(@year as varchar(max)) + '-' + cast(@month as varchar(max)) + '-1', 121)))
    end)
end

go

select 
    dbo.GetDateFromMonthYear(isStartDate, Month, Year) 
from (values
    (0, 2, 2016),
    (0, 3, 2017),
    (1, 4, 2017),
    (1, 12, 2017)
)t(isStartDate, Month, Year)

#5

Here's an example, using dateadd. If the bit is true then take the first day of the month. If the bit is false then add a month and subtract a day.

这是一个使用dateadd的例子。如果该位为真，则采用该月的第一天。如果该位为false，则添加一个月并减去一天。

DECLARE @sample TABLE ([year] int, [month] int, isStartDate bit)
INSERT INTO @sample VALUES (2017, 11, 1), (2017, 11, 0), (2017, 12, 0)

SELECT CASE WHEN isStartDate = 1
             THEN CONVERT(varchar, CAST(CAST(year as varchar) + CAST(month as varchar) + '01' as date), 105)
             ELSE CONVERT(varchar, DATEADD(DAY, -1, DATEADD(MONTH, 1, CAST(CAST(year as varchar) + CAST(month as varchar) + '01' as date))), 105)
       END
  FROM @sample

Produces:

生产：

01-11-2017
30-11-2017
31-12-2017