Date and Time Functions in SQL SERVER Part II || DATEDIFF , DATEFROMPARTS and DATENAME
This video will provide you the details of each Date and Time Data Types and Function in SQL Server 2014.
Last session for date other date functions:
• Date and Time Functions in SQL SERVER...
Date and Time Data Types and Functions :-
DATEDIFF (Transact-SQL) / DATEDIFF_BIG
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
Syntax
DATEDIFF ( datepart , startdate , enddate )
Arguments
datepart
Is the part of startdate and enddate that specifies the type of boundary crossed. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.
startdate
Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable or string literal. startdate is subtracted from enddate.
To avoid ambiguity, use four-digit years. For information about two digits years, see Configure the two digit year cutoff Server Configuration Option.
enddate
See startdate.
Return Type
Int
*****************************************************************************
DATEFROMPARTS (Transact-SQL)
Returns a date value for the specified year, month, and day.
Syntax
DATEFROMPARTS ( year, month, day )
Arguments
year
Integer expression specifying a year.
month
Integer expression specifying a month, from 1 to 12.
day
Integer expression specifying a day.
Return Types
Date
******************************************************************************
DATENAME (Transact-SQL)
Returns a character string that represents the specified datepart of the specified date
For an overview of all Transact-SQL date and time data types and functions,
Syntax
DATENAME ( datepart , date )
Arguments
datepart
Is the part of the date to return. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.
datepart Abbreviations Return values
year yy, yyyy 2007
quarter qq, q 4
month mm, m October
dayofyear dy, y 303
day dd, d 30
week wk, ww 44
weekday dw, w Tuesday
hour hh 12
minute mi, n 15
second ss, s 32
millisecond ms 123
microsecond mcs 123456
nanosecond ns 123456700
TZoffset tz 310
ISO_WEEK ISOWK, ISOWW 44
date
Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal.
To avoid ambiguity, use four-digit years. For information about two-digit years,
Return Type
Nvarchar
Return Value
• Each datepart and its abbreviations return the same value.
The return value depends on the language environment set by using SET LANGUAGE and by the Configure the default language Server Configuration Option of the login. The return value is dependant on SET DATEFORMAT if date is a string literal of some formats. SET DATEFORMAT does not affect the return value when the date is a column expression of a date or time data type.
When the date parameter has a date data type argument, the return value depends on the setting specified by using SET DATEFIRST.
If the data type of the date argument does not have the specified datepart, the default for that datepart will be returned only when a literal is specified for date.
For example, the default year-month-day for any date data type is 1900-01-01. The following statement has date part arguments for datepart, a time argument for date, and returns 1900, January, 1, 1, Monday.
SELECT DATENAME(year, '12:10:30.123')
,DATENAME(month, '12:10:30.123')
,DATENAME(day, '12:10:30.123')
,DATENAME(dayofyear, '12:10:30.123')
,DATENAME(weekday, '12:10:30.123');