DateName,Datetimeformparts(2012),Eomonth(2012)

--Today is 12th November 2013.

SELECT DATENAME(dayofyear, getdate())--316

SELECT DATENAME(MONTH, getdate())--November

SELECT DATENAME(DAY, getdate())--12

SELECT DATENAME(DD, getdate())--12

SELECT DATENAME(DW, getdate())--tuesday

SELECT DATENAME(WEEKDAY, getdate())--tuesday

SELECT DATENAME(D, getdate())--12

SELECT DATENAME(DY, getdate())--316

SELECT DATENAME(HOUR, getdate())--19 (7:08 pm)

SELECT DATENAME(MINUTE, getdate())--8

SELECT DATENAME(YEAR, getdate())--2013

SELECT DATENAME(YYYY, getdate())--2013

SELECT DATENAME(Y, getdate())--316

SELECT DATENAME(SECOND, getdate())--33

SELECT DATENAME(ISO_WEEK, getdate())--46 (46th week,lets make it jan1,,it will be 1st week)

SELECT DATENAME(ISO_WEEK,'1/1/2013')--1 (cool)

SELECT DATENAME(QUARTER,getdate())--4 (4th quarter,,lets try for may month,,, it will be 2nd quarter)

SELECT DATENAME(QUARTER,'1/5/2013')--2 (cool)

  Eomonth 
 Select Eomonth(getdate())asMonth--2014-01-31

--------------------------------------------------------------

DATETIMEFROMPARTS

DATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a DATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

WHERE: year, month, day, hour, minute, seconds and milliseconds are integer expressions representing valid year, month, day, hour, minute, seconds and milliseconds respectively.
Return Type: DATETIME

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,

        @hour INT = 1, @minute INT = 20, @seconds INT = 14,

        @milliseconds INT = 878

SELECT DATETIMEFROMPARTS( @year, @month, @day,

        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'




o/p:

DATETIME
———————–
2013-06-23 01:20:14.877