Dateadd returning the wrong days in the month

Question

I have a stored procedure that runs to pull data for sales in a given month. It does not return 31 days on months that have 31 days. I need some help understanding the breakdown of the following string

(dateadd(dd,-(datepart(dd,getdate())),CONVERT(CHAR(8),GetDate(),112)))+'23:59:59')

I understand that CONVERT(CHAR(8),GetDate(),112) is taking the system date and converting to YYYYMMDD and that datepart(dd,getdate()) takes the system date and takes just the day part, but I cannot decipher the entire string.

Answer 1

There is a issue with the expression you have given. The bracket after 23:59:59 does not have a opening brace.

However what the expression intends to do is: (datepart(dd,getdate())) is getting the the current date's day part

CONVERT(CHAR(8),GetDate(),112)) is getting current date in YYYMMDD

(dateadd(dd,-(datepart(dd,getdate())),CONVERT(CHAR(8),GetDate(),112))) is subtracting the day part from today's date (see the negative sign).

Thus it is trying to get the first day of the current month. In case the result of the above expression crosses the last day of the previous month, it adds 23 hours 59 minutes.

The logic was probably intended to get the last day of the previous month. This adding of ~24 hours is creating confusion.

Answer 2

This will help you out:

declare @First datetime = dateadd(month,datediff(month,0,getdate()),0)
declare @Last  datetime = dateadd(second,-1,dateadd(month,datediff(month,0,getdate())+1,0))

@First checks for the number of months from ZERO to NOW, then adds that number of months to ZERO to give you the first moment of this month.

@Last works similarly, but adds one month to the number of months added to ZERO to give you the first moment of next month, then it subtracts 1 second from that date to give you the last second of the last day of this month.

If you need to go to MS, you can change the last bit to subtract 3 miliseconds instead of 1 second.