{"id":1099,"date":"2013-06-22T03:08:37","date_gmt":"2013-06-21T21:38:37","guid":{"rendered":"http:\/\/sqlhints.com\/?p=1099"},"modified":"2013-07-07T03:10:43","modified_gmt":"2013-07-06T21:40:43","slug":"eomonth-function-in-sql-server-2012","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2013\/06\/22\/eomonth-function-in-sql-server-2012\/","title":{"rendered":"EOMONTH FUNCTION IN SQL SERVER 2012"},"content":{"rendered":"

EOMONTH<\/strong> is one of the new built-in Date and Time function introduced as a Part of Sql Server 2012<\/strong>. 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.<\/em><\/p>\n

SYNTAX:<\/strong> EOMONTH ( start_date [, month_to_add ] )<\/p>\n

Parameters:<\/strong>
\nstart_date:<\/strong> Date Expression representing the date, for which the last date of the Month is returned
\nmonth_to_add<\/strong>: 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.<\/p>\n

Return Type:<\/strong> Date<\/p>\n

Example 1: EOMONTH – start_date as DATETIME type<\/strong><\/p>\n

DECLARE @date AS DATETIME = '2012\/06\/22'\r\nSELECT EOMONTH(@date) EOFMONTH<\/pre>\n
Result:<\/span>
\nEOFMONTH
\n———-
\n2012-06-30<\/p>\n
Example 2: Last date of the month in previous versions of Sql Server like Sql Server 2008, 2005, 2000.<\/strong><\/p>\n
DECLARE @date AS DATETIME = '2012\/06\/22'\r\nSELECT DATEADD(MONTH,datediff(MONTH,-1, @date),-1) EOFMONTH<\/pre>\n
Result:<\/span>
\nEOFMONTH
\n———————–
\n2012-06-30 00:00:00.000<\/p>\n
Example 3: EOMONTH – Start_date as valid date expression of type VARCHAR<\/strong><\/p>\n
DECLARE @date AS varchar(10)= '2012\/06\/22'\r\nSELECT EOMONTH(@date) EOFMONTH<\/pre>\n
Result:<\/span>
\nEOFMONTH
\n———-
\n2012-06-30<\/p>\n
Example 4: Current\/Previous\/Previou-to-Previous month’s lastdate using EOMONTH<\/strong><\/p>\n
DECLARE @date AS DATETIME = '2012\/06\/22'\r\nSELECT EOMONTH(@date) 'Current Month',\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 EOMONTH(@date, -1) 'Previous Month',\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 EOMONTH(@date, -2) 'Previous-to-Previous Month'<\/pre>\n
Result:<\/span>
\nCurrent Month Previous Month Previous-to-Previous Month
\n————- ————– ————————–
\n2012-06-30 2012-05-31 2012-04-30<\/p>\n
Example 5: Current\/Next\/Next-To-Next month’s last date using EOMONTH<\/strong><\/p>\n
DECLARE @date AS DATETIME = '2012\/06\/22'\r\nSELECT EOMONTH(@date) 'Current Month',\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 EOMONTH(@date, 1) 'Next Month',\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 EOMONTH(@date, 2) 'Next-to-Next Month'<\/pre>\n
Result:<\/span>
\nCurrent Month Next Month Next-to-Next Month
\n————- ———- ——————
\n2012-06-30 2012-07-31 2012-08-31<\/p>\n
Example 6: Start Date of the Month using EOMONTH<\/strong><\/p>\n
DECLARE @date AS DATETIME = '2012\/06\/22'\r\nSELECT DATEADD(DAY, 1, EOMONTH(@date, -1)) \r\n                AS 'Current Month Start Date'<\/pre>\n
Result:<\/span>
\nCurrent Month Start Date
\n————————
\n2012-06-01<\/p>\n
Example 7: Invalid start_date<\/strong><\/p>\n
SELECT EOMONTH('2012\/02\/31') EOFMONTH<\/pre>\n
Result:<\/span>
\nEOFMONTH
\n———-
\nMsg 241, Level 16, State 1, Line 2
\nConversion failed when converting date and\/or time from character string.<\/span><\/p>\n
You may like to read the below new built-in function’s introduced in Sql Server 2012:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n<\/table>\n","protected":false},"excerpt":{"rendered":"
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 ] ) … Continue reading EOMONTH FUNCTION IN SQL SERVER 2012<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[111,13,3,5],"tags":[992,113,112,35,55,114],"class_list":["post-1099","post","type-post","status-publish","format-standard","hentry","category-date-and-time-functions","category-functions","category-sql-server","category-sql-server-2012-sql-server","tag-date-and-time-functions","tag-end-of-month-in-sql-server-2012","tag-eofmonth","tag-new-feature-in-sql-server-2012","tag-sql-server-2012","tag-start-of-month-in-sql-server-2012"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-hJ","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/1099","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=1099"}],"version-history":[{"count":8,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/1099\/revisions"}],"predecessor-version":[{"id":1107,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/1099\/revisions\/1107"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=1099"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=1099"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=1099"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
New Built in Functions introduced in Sql Server<\/strong><\/td>\n
CONVERSION FUNCTIONS<\/strong><\/td>\n
PARSE<\/a><\/strong><\/td>\nTRY_PARSE<\/a> <\/strong><\/td>\n
TRY_CONVERT<\/a><\/strong><\/td>\n
STRING FUNCTIONS<\/strong><\/td>\n
CONCAT<\/a><\/strong><\/td>\nFORMAT<\/a><\/strong<\/td>\n
LOGICAL FUNCTIONS<\/strong><\/td>\n
CHOOSE<\/a><\/strong><\/td>\nIIF<\/a><\/strong<\/td>\n
DATE AND TIME FUNCTIONS<\/strong><\/td>\n
EOMONTH<\/a><\/strong><\/td>\n
DATEFROMPARTS<\/a><\/strong><\/td>\nDATETIMEFROMPARTS<\/a><\/strong<\/td>\n
SMALLDATETIMEFROMPARTS<\/a> <\/strong><\/td>\nDATETIME2FROMPARTS <\/strong<\/td>\n
TIMEFROMPARTS <\/strong><\/td>\nDATETIMEOFFSETFROMPARTS<\/strong<\/td>\n