CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
yuro
yuro

Reputation: 2229

How to get the last day of month (SQL Server)

I'm using SQL Server 2008 R2 and need to return all data with the last day of month through selected start- and enddate:

When the user select two dates something like:

Startdate: 2017-01-01

Enddate: 2017-22-02

The result have to be: 2017-31-01 AND 2017-22-02

I tried the following code, but I got the wrong result => 2017-28-02

SELECT
    DATEADD(d, -1, DATEADD(mm, DATEDIFF(m, 0,'2017-22-02') + 1, 0)) AS DiffDate,
    MEMBER_ID
FROM 
    dbo.tblOne
WHERE
    DATUM >= '2017-01-01'
AND
    DATUM <= '2017-22-02'

Could anyone has an idea what I'm doing wrong?!

Edit: I expect the following result:

    DiffDate   |  MemberID | ...
---------------------------------
    2017-01-31 |  CBK01
    2017-01-31 |  KKM05
    2017-01-31 |  ABC99
    2017-02-22 |  CBK01
    2017-02-22 |  KKM05
    2017-02-22 |  ABC99

Upvotes: 2

Views: 6062

Answers (3)

shA.t
shA.t

Reputation: 16968

I can suggest this method to get last date of a month of a date:

declare @date datetime = '2017-04-22';

select 
    dateadd(d, 
        -datepart(d,@date)  -- count of days of @date to get back
        ,dateadd(m,1,@date) -- get date of next month of @date
    ) lastDate

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

Leaving the date format out of the solution here but that is something you really need to resolve. As I understand it you simply need to find the last day of the previous month for a given date. This will do that using getdate() as the base. You could easily change getdate() to be a column in your data.

select LastDayOfPreviousMonth = dateadd(day, -1, dateadd(month, datediff(month, 0, GETDATE()), 0))

Upvotes: 0

Anton Krouglov
Anton Krouglov

Reputation: 3409

I am using UDF fn_DateSerial to simplify such date operations:

--sample value
Declare @Startdate datetime
set @Startdate = GetDate()

--last day of month
select DateAdd(month,  1, dbo.fn_DateSerial(Year(@Startdate),Month(@Startdate),1,0,0,0,0)) - 1

So basically I am striping day part (making it 1st day), then 1 month is added, and finally one day is subtracted.

This UDF was inspired by vb6 function of same name.

/* 
Function composes datetime from its parts - year, month, day, minute, etc.
*/
CREATE FUNCTION dbo.fn_DateSerial(@year int, @month int, @day int, @hour int, @minute int, @second int, @millisecond int) RETURNS datetime
BEGIN
  DECLARE @dt datetime, @dtStr varchar(255)
  --mm/dd/yyyy hh:mi:ss.mmm(24h)
  SET @dtStr = ''
  SET @dtStr = @dtStr + right('00'+convert(varchar(2), @month),2) + '/'
  SET @dtStr = @dtStr + right('00'+convert(varchar(2), @day),2) + '/'
  SET @dtStr = @dtStr + right('0000'+convert(varchar(4),@year),4)

  SET @dtStr = @dtStr + ' ' + right('00'+convert(varchar(2), @hour),2) + ':'
  SET @dtStr = @dtStr + right('00'+convert(varchar(2), @minute),2) + ':'
  SET @dtStr = @dtStr + right('00'+convert(varchar(2), @second),2) + '.'
  SET @dtStr = @dtStr + right('000'+convert(varchar(3), @millisecond),3)

  SET @dt = CONVERT(datetime, @dtStr, 101)

  return @dt
END

Upvotes: 0

Related Questions