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
![img1.jpg]()