Sql Server 2012- Date time new function
Following are the seven new date time functions have been introduced in SQL Server 2012
1:- DATEFROMPARTS function
The DATEFROMPARTS function is introduced in SQL Server 2012. it returns a date value with the date parts set to the specified year, month and day.
Syntax: - DATEFROMPARTS(year, month, day)
Example:-
2:- DATETIME2FROMPARTS function
The DATETIME2FROMPARTS function returns a full datetime2 value.
Syntax: - DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)
Example:-
3:- DATETIMEFROMPARTS function
The DATETIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full datetime value.
Syntax:- DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds)
Example:-
4:- DATETIMEOFFSETFROMPARTS function
The DATETIMEOFFSETFROMPARTS function is introduced in SQL Server 2012. It returns a full datetimeoffset data type.
Syntax:-
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
Example:-
5:- SMALLDATETIMEFROMPARTS function
The SMALLDATETIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full smalldatetime value.
Syntax:- SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
Example:-
6:- TIMEFROMPARTS function
The TIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full time value.
Syntax:- TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
Example:-
7:- EOMONTH function
This is new datetime function that is introduced in sql server 2012. This function can be used to get the last day of the month. We can Use of EOMONTH() function to find last day Previous and Next Month.
Syntax:- EOMONTH (date)
Examples:-
Example 1: Find last day of the month
Example 2: Find last day of the month during Leap year
Example 3: Find last day Previous and Next Month
Following are the seven new date time functions have been introduced in SQL Server 2012
1:- DATEFROMPARTS function
The DATEFROMPARTS function is introduced in SQL Server 2012. it returns a date value with the date parts set to the specified year, month and day.
Syntax: - DATEFROMPARTS(year, month, day)
Example:-
DECLARE @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24 SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS Using_DATEFROMPARTS GO Output:- Using_DATEFROMPARTS ------------------- 2016-10-24
2:- DATETIME2FROMPARTS function
The DATETIME2FROMPARTS function returns a full datetime2 value.
Syntax: - DATETIME2FROMPARTS(year, month, day, hour, minute, seconds, fractions, precision)
Example:-
DECLARE @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24, @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45 SELECT DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 600, 3) AS Using_DATETIME2FROMPARTS GO OUTPUT:- Using_DATETIME2FROMPARTS ------------------------ 2016-10-24 17:44:45.600
3:- DATETIMEFROMPARTS function
The DATETIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full datetime value.
Syntax:- DATETIMEFROMPARTS(year, month, day, hour, minute, seconds, milliseconds)
Example:-
DECLARE @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24, @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45, @MILLISECONDS INT=0 SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS Using_DATETIMEFROMPARTS GO OUTPUT:- Using_DATETIMEFROMPARTS ------------------------ 2016-10-24 17:44:45.000
4:- DATETIMEOFFSETFROMPARTS function
The DATETIMEOFFSETFROMPARTS function is introduced in SQL Server 2012. It returns a full datetimeoffset data type.
Syntax:-
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
Example:-
DECLARE @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24, @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45 SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE,@SECONDS,500, 5, 30, 3) AS Using_DATETIMEOFFSETFROMPARTS GO OUTPUT:- Using_DATETIMEOFFSETFROMPARTS ----------------------------- 2016-10-24 17:44:45.500 +05:30
5:- SMALLDATETIMEFROMPARTS function
The SMALLDATETIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full smalldatetime value.
Syntax:- SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)
Example:-
DECLARE @YEAR INT = 2016, @MONTH INT = 10, @DAY INT = 24, @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45 SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS Using_SMALLDATETIMEFROMPARTS GO OUTPUT:- Using_SMALLDATETIMEFROMPARTS ---------------------------- 2016-10-24 17:44:00
6:- TIMEFROMPARTS function
The TIMEFROMPARTS function is introduced in SQL Server 2012. It returns a full time value.
Syntax:- TIMEFROMPARTS (hour, minute, seconds, fractions, precision)
Example:-
DECLARE @HOUR INT = 17, @MINUTE INT = 44, @SECONDS INT = 45 SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS Using_TIMEFROMPARTS GO OUTPUT:- Using_TIMEFROMPARTS ------------------- 17:44:45.500
7:- EOMONTH function
This is new datetime function that is introduced in sql server 2012. This function can be used to get the last day of the month. We can Use of EOMONTH() function to find last day Previous and Next Month.
Syntax:- EOMONTH (date)
Examples:-
Example 1: Find last day of the month
SELECT EOMONTH(GETDATE()) LastDayofMonth OUTPUT:- LastDayofMonth -------------- 2016-10-31
Example 2: Find last day of the month during Leap year
SELECT EOMONTH('20070201') LastDayofNonLeapYearFeb, EOMONTH('20080201') LastDayofLeapYearFeb, EOMONTH('20090201') LastDayofNonLeapYearFebLastDay; OUTPUT:- LastDayofNonLeapYearFeb LastDayofLeapYearFeb LastDayofNonLeapYearFebLastDay ------------------------------------------------------------------------------ 2007-02-28 2008-02-29 2009-02-28
Example 3: Find last day Previous and Next Month
SELECT EOMONTH(GETDATE(),-1) LastDayOfPreviousMonth, EOMONTH(GETDATE(),1) LastDayOfNextMonth OUTPUT:- LastDayOfPreviousMonth LastDayOfNextMonth -------------------------------------------- 2016-09-30 2016-11-30
No comments:
Post a comment