"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."


















Follow @MyTechMantra on Twitter
We're on Facebook





New Data and Time Functions in SQL Server 2012


Introduction

SQL Server 2012 has introduced new Date and Time functions in SQL Server 2012. This article explains each of them with along with an example.

Recommended:- Date and Time Functions in SQL Server 2008 / 2008 R2

New Data and Time Functions Introduced in SQL Server 2012 are:-

  • DATEFROMPARTS
  • DATETIMEFROMPARTS
  • DATETIME2FROMPARTS
  • TIMEFROMPARTS
  • SMALLDATETIMEFROMPARTS
  • DATETIMEOFFSETFROMPARTS
  • EOMONTH

Let take a look at each of them with an example.

Using DATEFROMPARTS Function in SQL Server 2012

DATEFROMPARTS function in SQL Server 2012 returns a date value for the specified Year, Month, and Day.

Syntax DATEFROMPARTS

DATEFROMPARTS (year, month, day)

SELECT DATEFROMPARTS (2012, 12, 12) AS DATEFROMPARTS
GO

DATEFROMPARTS
--------------------
2012-12-12

Using DATETIMEFROMPARTS Function in SQL Server 2012

DATETIMEFROMPARTS function in SQL Server 2012 returns a datetime value for the specified date and time.

Syntax DATETIMEFROMPARTS

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

SELECT DATETIMEFROMPARTS (2012, 12, 12, 12, 12, 12, 0) AS DATETIMEFROMPARTS
GO

DATETIMEFROMPARTS
---------------------------
2012-12-12 12:12:12.000

 

Using DATETIME2FROMPARTS Function in SQL Server 2012

DATETIME2FROMPARTS function in SQL Server 2012 returns a datetime2 value for the specified date and time and with the specified precision.

Syntax DATETIME2FROMPARTS

DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)

SELECT DATETIME2FROMPARTS (2012, 12, 12, 23, 59, 59, 500, 3) AS DATETIME2FROMPARTS
GO

DATETIME2FROMPARTS
--------------------------
2012-12-12 23:59:59.50

Using TIMEFROMPARTS Function in SQL Server 2012

TIMEFROMPARTS function in SQL Server 2012 returns a time value for the specified time and with the specified precision.

Syntax TIMEFROMPARTS

TIMEFROMPARTS (hour, minute, seconds, fractions, precision)

SELECT TIMEFROMPARTS (23, 59, 59, 0, 0) AS TIMEFROMPARTS
GO

TIMEFROMPARTS
-------------------
23:59:59

Using SMALLDATETIMEFROMPARTS Function in SQL Server 2012

SMALLDATETIMEFROMPARTS function in SQL Server 2012 Returns a smalldatetime value for the specified date and time.

Syntax SMALLDATETIMEFROMPARTS

SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)

SELECT SMALLDATETIMEFROMPARTS (2012, 12, 12, 23, 59) AS SMALLDATETIMEFROMPARTS
GO

SMALLDATETIMEFROMPARTS
---------------------------------
2012-12-12 23:59:00

Newsletter Signup

Using DATETIMEOFFSETFROMPARTS Function in SQL Server 2012

DATETIMEOFFSETFROMPARTS function in SQL Server 2012 Returns a smalldatetime value for the specified date and time.

Syntax DATETIMEOFFSETFROMPARTS

DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)

SELECT DATETIMEOFFSETFROMPARTS (2012, 12, 12, 12, 12, 12, 0, 8, 00, 3 ) AS DATETIMEOFFSETFROMPARTS
GO

DATETIMEOFFSETFROMPARTS
------------------------------------
2012-12-12 12:12:12.000 +08:00

Using EOMONTH Function in SQL Server 2012

DATETIMEOFFSETFROMPARTS function in SQL Server 2012 Returns a smalldatetime value for the specified date and time.

Syntax EOMONTH

EOMONTH ( start_date [, month_to_add ] )

DECLARE @TodaysDate DATETIME = GETDATE()
SELECT EOMONTH (@TodaysDate) AS 'This Month'
SELECT EOMONTH (@TodaysDate, 1) AS 'Next Month'
SELECT EOMONTH (@TodaysDate, -1) AS 'Last Month'
GO

This Month
----------
2012-03-31

Next Month
----------
2012-04-30

Last Month
----------
2012-02-28

Conclusion

This article gives you an overview of new date and time functions in SQL Server 2012. We would recommend you to try them and “Share this Article” with your colleagues and friends.

Share this Article

Related Articles…