New Date and Time (Built-in Functions) Functions in SQL Server 2012

Today, I have provided an article showing you how to use the new Date and Time functions in SQL Server. In this article, I will be covering the common usage of these functions because they are very useful for SQL developers. There are seven new datetime functions introduced in SQL Server 2012. These new functions are:

DATEFROMPARTS
TIMEFROMPARTS
DATETIMEFROMPARTS
DATETIME2FROMPARTS
SMALLDATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
EOMONTH

Let's have a look at a practical example. The example is developed in SQL Server 2012. These are the following arguments or parameters which is passed to DateTime functions:

Year - Integer expression that represents a year.
Month - Integer expression that represents a month.
Day - Integer expression that represents a day.
Hour - Integer expression that represents hours.
Minute - Integer expression that represents minutes.
Seconds - Integer expression that represents seconds.
Milliseconds - Integer expression that represents milliseconds.
Fractions - Integer expression that represents 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 that represents the hour portion of the time zone offset.
Minute_Offset - Integer expression that represents the minute portion of the time zone offset.
Precision - Integer literal that represents precision of the DATETIME2 value to be returned.

1. DATEFROMPARTS

This function returns a date value for the specified year, month, and day.

Syntax : This function contains 3 parameters:

DATEFROMPARTS (Param1,param2,param3)

Param1- For Year, Param2- For Month, Param3- For Day

2. TIMEFROMPARTS

This function returns a time value for the specified time and with the specified precision.

Syntax : This function contains 5 parameters:

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

3. DATETIMEFROMPARTS

This function returns a DateTime value with the date time part set to the specified year, specified month, specified day, specified hour, specified minute, specified second and the specified milliseconds.

Syntax : This function contains 7 parameters:

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

4. DATETIME2FROMPARTS

This function returns a datetime2 value for the specified date and time and with the specified precision.

Syntax : This function contains 8 parameters:

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

5. SMALLDATETIMEFROMPARTS

This function returns a small DateTime value for the specified date and time.

Syntax : This function contains 5 parameters:

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

6. DATETIMEOFFSETFROMPARTS

This function returns a datetimeoffset value for the specified date and time and with the specified offsets and precision. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

Syntax: This function contains 10 parameters:

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

7. EOMONTH

This function returns the last day of the month based on the date which is passed as an input parameter.

Syntax: This function contains 2 parameters:

EOMONTH (start_date [,month_to_add])

For Example

Declare @Y int=2012,

@M int=6,

@D int =3,

@s int=20,

@H int = 11,

@Minute int = 59,

@Second INT = 59,

@MILLISECONDS INT = 2,

@StartDate DATETIME = GETDATE() -- For Emonth Function

-- DATEFROMPARTS Function

SELECT DATEFROMPARTS (@Y, @M, @D) AS [DATEFROMPARTSResult]

-- TIMEFROMPARTS Function

SELECT TIMEFROMPARTS(@H, @Minute, @Second, 500, 3) AS [TimeFROMPARTSResult]

-- DATETIMEFROMPARTS Function

Select DATETIMEFROMPARTS(@Y,@M,@D,@H,@Minute,@Second,@MILLISECONDS) AS [DATETIMEFROMPARTSResult]

--DATETIME2FROMPARTS Function

SELECT DATETIME2FROMPARTS (@Y,@M,@D,@H,@Minute,@Second, 500, 3) AS [DATETIME2FROMPARTSResult]

-- SMALLDATETIMEFROMPARTS Function

Select SMALLDATETIMEFROMPARTS(@Y,@M,@D,@H,@Minute) AS [SMALLDATETIMEFROMPARTSResult]

-- SMALLDATETIMEFROMPARTS Function

Select DATETIMEOFFSETFROMPARTS(@Y,@M,@D,@H,@Minute,@Second, 500, 3, 40, 4) AS [DATETIMEOFFSETFROMPARTSResult]

-- EOMONTH Function

Select EOMONTH(@StartDate) AS [EOMONTHResult]

OUTPUT