CODEWITHSUNDEEP
sql-serverperformancesql-server-2008date
ankur
ankur

Reputation: 4733

Create Date From Month and Year in 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.

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

Output: 01-11-2011

Output: 30-11-2011

Output: 31-12-2011

Upvotes: 0

Views: 3194

Answers (5)

Caius Jard
Caius Jard

Reputation: 74730

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

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.

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

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...

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)

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

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

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.

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

Upvotes: 3

Zorkolot
Zorkolot

Reputation: 2027

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.

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

Upvotes: 0

Alexey
Alexey

Reputation: 919

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)

Upvotes: 0

Ben Thul
Ben Thul

Reputation: 32737

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.

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

Upvotes: 0

Related Questions