Date Functions in SQL SERVER Part III || DATEPART, DATETIME2FROMPARTS, DATETIMEFROMPARTS and EOMONTH
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:
Part I
• Date and Time Functions in SQL SERVER Part...
Part II
• Date and Time Functions in SQL SERVER Part...
*************************************************************************
DATEPART (Transact-SQL)
Returns an integer that represents the specified datepart of the specified date.
Syntax
DATEPART ( datepart , date )
Arguments
datepart
Is the part of date (a date or time value) for which an integer will be returned. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.
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.
Return Type
Int
*************************************************************************
DATETIME2FROMPARTS (Transact-SQL)
Returns a datetime2 value for the specified date and time and with the specified precision.
Syntax
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )
Arguments
year
Integer expression specifying a year.
month
Integer expression specifying a month.
Softtechforum
day
Integer expression specifying a day.
hour
Integer expression specifying hours.
minute
Integer expression specifying minutes.
seconds
Integer expression specifying seconds.
fractions
Integer expression specifying fractions.
precision
Integer literal specifying the precision of the datetime2 value to be returned.
Return Types
datetime2( precision )
Examples
• Example without fractions of a second
SELECT DATETIME2FROMPARTS ( 2016, 12, 31, 23, 59, 59, 0, 0 ) AS Result;
• Example with fractions of a second
The following example demonstrates the use of the fractions and precision parameters:
1. When fractions has a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.
2. When fractions has a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.
3. When fractions has a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.
Transact-SQL
SELECT DATETIME2FROMPARTS (2016, 8, 15, 14, 23, 44, 5, 1 );
SELECT DATETIME2FROMPARTS (2016, 8, 15, 14, 23, 44, 50, 2 );
SELECT DATETIME2FROMPARTS (2016, 8, 15, 14, 23, 44, 500, 3 );
*************************************************************************
DATETIMEFROMPARTS (Transact-SQL)
Returns a datetime value for the specified date and time.
Syntax
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )
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.
Return Types
datetime
Examples
SELECT DATETIMEFROMPARTS ( 2016, 12, 31, 23, 59, 59, 0 ) AS Result;
*************************************************************************
DAY/YEAR/MONTH (Transact-SQL)
Returns an integer representing the day (day of the month)/year/month of the specified date.
Syntax
DAY ( date ) / YEAR ( date ) / MONTH ( date )
Arguments
date
Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable or string literal.
Return Type
Int
Return Value
DAY returns the same value as DATEPART (day, date). Same with DATEPART(month,date) and DATEPART(year,date)
If date contains only a time part, the return value is 1, the base day.
*************************************************************************
EOMONTH (Transact-SQL)
Returns the last day of the month that contains the specified date, with an optional offset.
Syntax
EOMONTH ( start_date [, month_to_add ] )
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.
If this argument is specified, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then an error is raised.
Return Type
date