SQL datetime needs to read 00:00:00.000

I have the following piece of SQL:

select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

​x
 
select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))​

which comes through as this format:

2012-02-29 23:59:59.000

 
2012-02-29 23:59:59.000​

I need the exact piece of code with the date the same, however the time part must read 00:00:00.000

Modify* I should have been clear here: I need to have the last day of previous month at any given time (with the time in 00:00:00.000 format of course)

Answer

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

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

Advertisement

Answer