candide (2/25/2013)

---

Hi,
i have a view with two columns, lets say SpecYear and SpecMonth, both are integer.
How can I build two new columns with the first and last day of this year/month

ex.
2013 02 => 2013-02-01 2013-02-28
etc.

thanx

declare @Month int = 2, @Year int = 2013

declare @ThisDate datetime
set @ThisDate = cast(@Year as char(4)) + right('0' + cast(@Month as varchar(2)), 2) + '01'
select @ThisDate

select dateadd(mm, datediff(mm, 0, @ThisDate), 0)     -- Beginning of this month
select dateadd(day, -1, dateadd(mm, datediff(mm, 0, @ThisDate) + 1, 0)) -- End of this month

DECLARE @ThisYear INT,
        @ThisMonth INT;
SET @ThisYear = 2013;
SET @ThisMonth = 2;

SELECT
    DATEADD(MONTH, @ThisMonth - 1, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME))) BOM,
    DATEADD(DAY, -1, DATEADD(MONTH, @ThisMonth, DATEADD(YEAR, @ThisYear - 1900, CAST('19000101' AS DATETIME)))) EOM

DECLARE @ThisYear  INT,
        @ThisMonth INT;
 SELECT @ThisYear  = 2013,
        @ThisMonth = 2;

 SELECT BOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth-1, 0),
        EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth  ,-1)

DECLARE @ThisYear  INT,
        @ThisMonth INT;
 SELECT @ThisYear  = 2012, --Leap Year!
        @ThisMonth = 2;

 SELECT BOM = DATEADD(mm, @ThisYear*12-22801+@ThisMonth, 0),
        EOM = DATEADD(mm, @ThisYear*12-22800+@ThisMonth,-1)

declare @Month int = 2, @Year int = 2013
declare @ThisDate datetime = convert(varchar(8),@year*10000+@month*100+01)

select  CONVERT(varchar(7),@thisdate,121)+'-01'
select  CONVERT(varchar(7),dateadd(mm,1,@thisdate),121)+'-01'

SELECT BOM = @ThisYear * 10000 + @ThisMonth * 100 + 1,

DECLARE @BOM datetime
SELECT @BOM = CAST(@ThisYear * 10000 + @ThisMonth * 100 + 1 AS char(8))
SELECT @BOM