Calculate last day of month using sql server

There are couple of ways to calculate last day of a given month.

SQL Server 2012 on wards there is inbuilt function find the last day of month.

Let's see different ways with example:

Using SQL Server in built function.

select EOMONTH(getdate(),0) as curr_month

output:

curr_month

2020-07-31

select EOMONTH(getdate(),-1) as  Prev_month

output:

Prev_month

2020-06-30

Note: 0,-1, -2 can be used to navigate among months.

Let's see how to find last day of month with out using the function

EX1:

select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,0,getdate())),-1) as curr_month

EX2: (best approach)

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, getdate()) +1, 0)) as curr_month

curr_month

2020-07-31 00:00:00.000

Ex1:

select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,-1,getdate())),-1) as Prev_month

Ex2:

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, getdate()) , 0)) as Prev_month

Prev_month

2020-06-30 00:00:00.000

Highlighted 0,-1 is being used to navigate among months

The above script can be converted to t-sql scripts

Declare @date datetime

set @date='2020-07-21'

 select DATEADD(MM,1+DATEDIFF(mm,0,DATEADD(m,0,@date)),-1) as curr_month

or

select DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @date) +1, 0)) as curr_month

curr_month

2020-07-31 00:00:00.000

How to insert a string value into column with an single quote (apostrophe )

While tried to insert string value having single quote (apostrophe) character in sql server, it will not allow you to insert. it will throw error below.

Please follow below steps to resolve the issue.

Step 1: Create table with two columns

USE tempdb

GO

CREATE TABLE single_quote

(

  [ID] INT,

  [Name] VARCHAR(50)

)

GO

Step 2: Try to insert string with single quote value

INSERT INTO single_quote VALUES (1,'o'reilly books')

GO

Msg 102, Level 15, State 1, Line 10

Incorrect syntax near 'reilly'.

Msg 105, Level 15, State 1, Line 10

Unclosed quotation mark after the character string ')

GO

'.

Step 3: Replace Single quote with double single quote

INSERT INTO single_quote VALUES (1,'o''reilly books')

GO

Step 4: Check the value

select * from single_quote

ID Name

1 o'reilly books

Now you can see the value got inserted correctly.

Watch the Video Here: