As SQL Servers built-in date functions are lacking, and you usually need them often in various databases, create a separate database where you define various UDF's (User defined Functions). (You can create UDF's in System too, but there may be complications.)
E.g. you create database Utiities. In Utilities.Programmability.Functions.Scalar-valued Functions you create a function udf_Date
Code:
USE [utilities]
GO...SET ANSI_NULLSON
GO
SET QUOTED_IDENTIFIERON
GO
...
CREATE FUNCTION [dbo].[udf_Date]
(
-- Add the parameters for the function here
@Year INT, @Month INT, @Day INT
)
RETURNS datetime
AS
BEGIN
-- Declare the return variable here
DECLARE @Date DATETIME
-- Add the T-SQL statements to compute the return value here
SET @Date =
(CASE
WHEN @Month = 2 AND @Day >28 AND @Day < 31 THEN
DateAdd(yyyy,@Year-1900,DateAdd(m,@Month,0))- 1 -DateAdd(yyyy,@Year-1900,DateAdd(m,@Month-1,31-1))
ELSE 0
END)+
DateAdd(yyyy,@Year-1900,DateAdd(m,@Month-1,@Day-1))
-- RETURN @Date
RETURN @Date
END
GO
You have to allow all users access to database Utilities.
Now your 1st formula will be
Code:
utilities.dbo.udf_Date(Year([Date_in]),Month(([Date_in]),1)
The code used in another one ( a function which adds a number of days to today's date, you can easily change it to add a number of days to any date - add a parameter @Shift to function udf_Date(), an add last SET expression from udf_FromToday)
Code:
...CREATE FUNCTION [dbo].[udf_FromToday]
(-- Add the parameters for the function here
@Shift int= 0
)
DECLARE @Date DATETIME
-- Add the T-SQL statements to compute the return value here
DECLARE @Year smallint
DECLARE @Month smallint
DECLARE @Day smallint
SET @Year =YEAR(GetDate())
SET @Month =MONTH(GetDate())
SET @Day =DAY(GetDate())
SET @Date = utilities.dbo.udf_Date(@Year, @Month, @Day + @Shift)
-- Return the result of the function
RETURN @Date
END