Tag Archives: Date and Time Functions

DATETIMEFROMPARTS FUNCTION IN SQL SERVER 2012

DATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a DATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

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

WHERE: year, month, day, hour, minute, seconds and milliseconds are integer expressions representing valid year, month, day, hour, minute, seconds and milliseconds respectively.
Return Type: DATETIME

Example 1: DATETIMEFROMPARTS – with valid date and time part integer constants

SELECT
 DATETIMEFROMPARTS(2013,6,23,1,20,14,8) AS 'DATETIME',
 DATETIMEFROMPARTS(1753,1,1,0,0,0,0) AS 'MIN DATETIME',
 DATETIMEFROMPARTS(9999,12,31,23,59,59,997) AS 'MAX DATETIME'

Result:

DATETIME                               MIN DATETIME                           MAX DATETIME
———————–                      ———————–                        ———————–
2013-06-23 01:20:14.007       1753-01-01 00:00:00.000        9999-12-31 23:59:59.997

Example 2: DATETIMEFROMPARTS – with valid date and time part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:

DATETIME
———————–
2013-06-23 01:20:14.877

Example 3: DATETIMEFROMPARTS – with Invalid part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = 14, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @invalidmonth, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
Msg 289, Level 16, State 3, Line 3
Cannot construct data type datetime, some of the arguments have values which are not valid.

Example 4: DATETIMEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
        @hour INT = 1, @minute INT = 20, @seconds INT = NULL, 
        @milliseconds INT = 878
SELECT DATETIMEFROMPARTS( @year, @month, @day, 
        @hour , @minute, @seconds, @milliseconds) AS 'DATETIME'

Result:
DATETIME
———————–
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

SMALLDATETIMEFROMPARTS FUNCTION IN SQL SERVER 2012

SMALLDATETIMEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. Returns a SMALLDATETIME value for the specified date and time. It is a Sql Server native function not dependent on the .NET CLR.

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

WHERE: year, month, day, hour and minute are integer expressions representing valid year, month, day, hour and minute respectively.
Return Type: SMALLDATETIME

Example 1: SMALLDATETIMEFROMPARTS – with valid date and time part integer constants

SELECT 
 SMALLDATETIMEFROMPARTS(2013,6,23,1,20 ) AS 'SMALLDATETIME',
 SMALLDATETIMEFROMPARTS(1900,1,1,0,0) AS 'MIN SMALLDATETIME',
 SMALLDATETIMEFROMPARTS(2079,6,6,23,59) AS 'MAX SMALLDATETIME'

Result:

SMALLDATETIME         MIN SMALLDATETIME         MAX SMALLDATETIME
———————–           ———————–                   ———————–
2013-06-23 01:20:00   1900-01-01 00:00:00             2079-06-06 23:59:00

Example 2: SMALLDATETIMEFROMPARTS – with valid date and time part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23,
		@hour INT = 1, @minute INT = 20
SELECT SMALLDATETIMEFROMPARTS( @year, @month, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:

SMALLDATETIME
———————–
2013-06-23 01:20:00

Example 3: SMALLDATETIMEFROMPARTS – with Invalid part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
		@hour INT = 1, @minute INT = 20
SELECT SMALLDATETIMEFROMPARTS( @year, @invalidmonth, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:
Msg 289, Level 16, State 4, Line 3
Cannot construct data type smalldatetime, some of the arguments have values which are not valid.

Example 4: SMALLDATETIMEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23,
		@hour INT = 1, @minute INT = NULL
SELECT SMALLDATETIMEFROMPARTS( @year, @invalidmonth, @day, 
		@hour , @minute) AS 'SMALLDATETIME'

Result:
SMALLDATETIME
———————–
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

DATEFROMPARTS FUNCTION IN SQL SERVER 2012

DATEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. As name suggests it forms a DATE from it’s parts i.e. Year, Month and Day. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATEFROMPARTS ( year, month, day )

WHERE: year, month and day are integer expressions representing valid year, month and day respectively.
Return Type: DATE

Example 1: DATEFROMPARTS – with valid date part integer constants

SELECT DATEFROMPARTS( 2013, 6, 23 ) AS 'DATE',
	DATEFROMPARTS( 1, 1, 1 ) AS 'MIN DATE',
	DATEFROMPARTS( 9999, 12, 31 ) AS 'MAX DATE'

Result:
DATE                    MIN DATE                 MAX DATE
———-                ———-                       ———-
2013-06-23       0001-01-01              9999-12-31

Example 2: DATEFROMPARTS – with valid date part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
2013-06-23

Example 3: DATEFROMPARTS – with invalid month part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23
SELECT DATEFROMPARTS( @year, @invalidmonth, @day) AS 'DATE'

Result:
Msg 289, Level 16, State 1, Line 2
Cannot construct data type date, some of the arguments have values which are not valid.

Example 4: DATEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = NULL
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS

EOMONTH FUNCTION IN SQL SERVER 2012

EOMONTH is one of the new built-in Date and Time function introduced as a Part of Sql Server 2012. It returns the last date of the Month that contains the specified date in it. It is a Sql Server native function, not dependent on the .NET CLR.

SYNTAX: EOMONTH ( start_date [, month_to_add ] )

Parameters:
start_date: Date Expression representing the date, for which the last date of the Month is returned
month_to_add: This is an optional integer parameter, if it is specified then specified number of months is added to the start_date and then last date of the month for the resultant date is returned.

Return Type: Date

Example 1: EOMONTH – start_date as DATETIME type

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) EOFMONTH

Result:
EOFMONTH
———-
2012-06-30

Example 2: Last date of the month in previous versions of Sql Server like Sql Server 2008, 2005, 2000.

DECLARE @date AS DATETIME = '2012/06/22'
SELECT DATEADD(MONTH,datediff(MONTH,-1, @date),-1) EOFMONTH

Result:
EOFMONTH
———————–
2012-06-30 00:00:00.000

Example 3: EOMONTH – Start_date as valid date expression of type VARCHAR

DECLARE @date AS varchar(10)= '2012/06/22'
SELECT EOMONTH(@date) EOFMONTH

Result:
EOFMONTH
———-
2012-06-30

Example 4: Current/Previous/Previou-to-Previous month’s lastdate using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) 'Current Month',
                EOMONTH(@date, -1) 'Previous Month',
                EOMONTH(@date, -2) 'Previous-to-Previous Month'

Result:
Current Month Previous Month Previous-to-Previous Month
————- ————– ————————–
2012-06-30 2012-05-31 2012-04-30

Example 5: Current/Next/Next-To-Next month’s last date using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT EOMONTH(@date) 'Current Month',
                EOMONTH(@date, 1) 'Next Month',
                EOMONTH(@date, 2) 'Next-to-Next Month'

Result:
Current Month Next Month Next-to-Next Month
————- ———- ——————
2012-06-30 2012-07-31 2012-08-31

Example 6: Start Date of the Month using EOMONTH

DECLARE @date AS DATETIME = '2012/06/22'
SELECT DATEADD(DAY, 1, EOMONTH(@date, -1)) 
                AS 'Current Month Start Date'

Result:
Current Month Start Date
————————
2012-06-01

Example 7: Invalid start_date

SELECT EOMONTH('2012/02/31') EOFMONTH

Result:
EOFMONTH
———-
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.

You may like to read the below new built-in function’s introduced in Sql Server 2012:

New Built in Functions introduced in Sql Server
CONVERSION FUNCTIONS
PARSE TRY_PARSE
TRY_CONVERT
STRING FUNCTIONS
CONCAT FORMAT
LOGICAL FUNCTIONS
CHOOSE IIF
DATE AND TIME FUNCTIONS
EOMONTH
DATEFROMPARTS DATETIMEFROMPARTS
SMALLDATETIMEFROMPARTS DATETIME2FROMPARTS
TIMEFROMPARTS DATETIMEOFFSETFROMPARTS