0 SQL Server 2012 New Date Functions

SQL Server 2008 introduced 4 new data types associated with the date and time, namely, the DATE, TIME, DATETIME2 and DATETIMEOFFSET data types.  The DATE data types stores only a date value while the TIME data type defines a time of a day without time zone awareness and is based on a 24-hour clock.  The DATETIME2 data type defines a date that is combined with a time of day that is based on a 24-hour clock and has a larger date range, a larger default fractional precision and an optional user-specified precision.  Lastly, the DATETIMEOFFSET data type defines a date that is combined with a time of day that has time zone awareness and is based on a 24-hour clock.

SQL Server 2012, on the other hand, is not introducing any new date and time related data types but is introducing a few new date and time functions that return a date and/or time value from their parts.

Function Name	Description	Syntax	Return Data Type
DATEFROMPARTS	Returns a DATE value for the specified year, month and day.	DATEFROMPARTS( <year>, <month>, <day> )	DATE
DATETIME2FROMPARTS	Returns a DATETIME2 value for the specified date and time and with the specified precision.	DATETIME2FROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <precision> )	DATETIME2
DATETIMEFROMPARTS	Returns a DATETIME value for the specified date and time.	DATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <milliseconds> )	DATETIME
DATETIMEOFFSETFROMPARTS	Returns a DATETIMEOFFSETvalue for the specified date and time and with the specified offsets and precision.	DATETIMEOFFSETFROMPARTS( <year>, <month>, <day>, <hour>, <minute>, <seconds>, <fractions>, <hour_offset>, <minute_offset>, <precision> )	DATETIMEOFFSET
SMALLDATETIMEFROMPARTS	Returns a SMALLDATETIMEvalue for the specified date and time.	SMALLDATETIMEFROMPARTS( <year>, <month>, <day>, <hour>, <minute> )	SMALLDATETIME
TIMEFROMPARTS	Returns a TIME value for the specified time and with the specified precision.	TIMEFROMPARTS( <hour>, <minute>, <seconds>, <fractions>, <precision> )	TIME

Parameters / Arguments :

• <year> - Integer expression specifying a year.
• <month> - Integer expression specifying a month.
• <day> - Integer expression specifying a day.
• <hour> - Integer expression specifying hours.
• <minute> - Integer expression specifying minutes.
• <seconds> - Integer expression specifying seconds.
• <milliseconds> - Integer expression specifying milliseconds.
• <fractions> - Integer expression specifying fractions.  This parameter depends on the <precision> parameter.  For example, if <precision> is 7, then each fraction represents 100 nanoseconds; if <precision> is 3, then each fraction represents a millisecond.
• <hour_offset> - Integer expression specifying the hour portion of the time zone offset.
• <minute_offset> - Integer expression specifying the minute portion of the time zone offset.
• <precision> - Integer literal specifying the precision of the DATETIME2 value to be returned.

In addition to these date/time functions that returns date and time values from their parts, one other new date/time function is the EOMONTH which returns the last day of the month that contains the specified date, with an optional offset.

Function Name	Description	Syntax	Return Data Type
EOMONTH	Returns the last day of the month that contains the specified date, with an optional offset.	EOMONTH( <start_date> [, <month_to_add>] )	Date type of <start_date> or DATETIME2(7)

Parameters / Arguments :

• <start_date> - Date expression specifying the date for which to return the last day of the month.
• <month_to_add> - Optional integer expression specifying the number of months to add to <start_date>.

0 SQL Server DATEDIFF() Function

Definition and Usage

The DATEDIFF() function returns the time between two dates.

Syntax

DATEDIFF(datepart,startdate,enddate)

Where startdate and enddate are valid date expressions and datepart can be one of the following:

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

---

Example

Now we want to get the number of days between two dates.

We use the following SELECT statement:

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

Result:

DiffDate
61

Example

Now we want to get the number of days between two dates (notice that the second date is "earlier" than the first date, and will result in a negative number).

We use the following SELECT statement:

SELECT DATEDIFF(day,'2008-08-05','2008-06-05') AS DiffDate

Result:

DiffDate
-61

Source : w3schools.com