SQL datetime needs to read 00:00:00.000

select dateadd(d,datediff(d,0,dateadd(s,-1,dateadd(m,datediff(m,0,getdate()),0))),0)

SELECT DATEADD(MONTH, -1, DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())))

This will give you the last second of the prior month

select dateadd(s,-1,dateadd(month,datediff(month,0,GETDATE()),0));

and this will give you the last day of the prior month

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

More details of how to do this:

select dateadd(day,datediff(day,0,@datetime),0);

select dateadd(day,datediff(day,0,GETDATE()),0);

In English: Take the number of days between this date and 0 and add those days to 0.

This works with any parameter for datediff. So

select dateadd(month,datediff(month,0,GETDATE()),0);

Will "remove" all day information in addition to time information.

Related