Date Functions

-- End of the Month

SELECT EOMONTH('12/25/2011')

--you can add months also using this function by passing duration in second parameter.

DECLARE @Currentdate DATETIME
SET @Currentdate = '12/12/2010'
SELECT EOMONTH ( @Currentdate,-2 ) AS Result;

-- DATEFROMPARTS function to create date based on specified month,year and day

DECLARE @Year INT,
@Month INT,
@Day INT
SET @Year = 2011
SET @Month = 11
SET @Day = 29

SELECT DATEFROMPARTS (@Year,@Month,@Day) as Result

-- TIMEFROMPARTS function to create time based on specified Hour,Minute and Second

DECLARE @Hour INT,
@Minute INT,
@Seconds INT,
@Fractions INT
SET @Hour = 15
SET @Minute = 20
SET @Seconds = 50
SET @Fractions = 500

SELECT TIMEFROMPARTS (@Hour, @Minute, @Seconds, @Fractions, 3) as Result

-- Note: Last Argument of this function should be integer constant and it indicates number of digits in milliseconds part. It can be between 3 to 7 digits.

-- This function returns a fully qualified DATETIME value based on the specified datetime parts such as year, month, day, hour, minute, seconds and milliseconds.

DECLARE
@Year INT = 2011,
@Month INT = 11,
@Day INT = 29,
@Hour INT = 15,
@Minute INT = 20,
@Seconds INT = 50,
@MilliSeconds INT = 0

SELECT DATETIMEFROMPARTS(
@Year,
@Month,
@Day,
@Hour,
@Minute,
@Seconds,
@MilliSeconds) as Result