SQL datetime needs to read 00:00:00.000

Question

I have the following piece of SQL:

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

which comes through as this format:

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)

possible duplicate of [Floor a date in SQL server](http://stackoverflow.com/questions/85373/floor-a-date-in-sql-server) — Hogan, Mar 29 '12 at 11:01
`SELECT CAST(s AS DATE)` or `SELECT CAST(CAST(s As Date) As DateTime)`? — Seph, Mar 29 '12 at 11:35

score 5 · Accepted Answer · answered Mar 29 '12 at 10:54

5

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

answered Mar 29 '12 at 10:54

general exception

4,202
9
54
82

Darren · Answer 2 · 2012-03-29T10:57:37.260

3

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

edited Mar 29 '12 at 10:57

answered Mar 29 '12 at 10:52

Darren

68,902
24
138
144

OP's query gives the last day / second of the previous month. Your query gives the 1st second of today. – StuartLC Mar 29 '12 at 10:55
2

This wont work, as he is subtracting a month from the current month. – general exception Mar 29 '12 at 10:56
SELECT DATEADD(MONTH, -1, DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))) – Darren Mar 29 '12 at 10:57
This removes the time part and goes back one month. Try it for date `2012-03-14T10:00:00` and you will see that it does not give `2012-02-29 00:00:00.000` as output. – Mikael Eriksson Mar 29 '12 at 11:30
FYI he want's it to go back one month. The answer is correct – Darren Mar 29 '12 at 11:34
You have misunderstood the question. He wants `2012-02-29 00:00:00.000` for **all** dates in March. – Mikael Eriksson Mar 29 '12 at 11:52
@general exception - yes, 1 hour ago when the question was first posted. I updated it to reflect the answer in the comment :) – Darren Mar 29 '12 at 12:10
@MikaelEriksson - marked as the answer, must have been what he was looking for. Thanks for your input. – Darren Mar 29 '12 at 12:27
@general exception marked as the answer, must have been what he was looking for. Thanks for your input. – Darren Mar 29 '12 at 12:27
@DarrenDavies - I think not. Your query will give the answer he wants today, tomorrow and on Saturday but on Sunday he will be disappointed. – Mikael Eriksson Mar 29 '12 at 12:37
@MikaelEriksson OP has modified question, looks like he wants to return the last day of the previous month in the said format (without time). – general exception Mar 29 '12 at 12:49
1

@generalexception - Yes, that is what I thought all the time. And you as well since your answer does exactly that. – Mikael Eriksson Mar 29 '12 at 13:02

Hogan · Answer 3 · 2012-03-29T11:18:23.517

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);

or

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.

score 2 · Answer 4 · answered Mar 29 '12 at 10:57

2

An alternative method to strip out the time portion is to cast it to a float, apply the Floor function and cast back to a datetime.

select Cast(Floor(Cast(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as float)) as datetime)

answered Mar 29 '12 at 10:57

codingbadger

42,678
13
95
110

score 0 · Answer 5 · edited Mar 15 '20 at 10:19

0

Saw some similar posts

select  cast(cast(dateadd(dd,-1,getdate()) as date) as datetime)

Cast your dateadd as a date and then enclose it in another cast back to datetime

So it goes from this 2012-02-29 23:59:59.000

To this 2012-02-29

and the finally this 2012-02-29 00:00:00.000

edited Mar 15 '20 at 10:19

Dharman

30,962
25
85
135

answered Nov 23 '19 at 00:04

Kenneth Wilson

1
4

score 0 · Answer 6 · answered Mar 29 '12 at 11:24

0

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

In SQL Server 2012 you could use eomonth.

SELECT EOMONTH(DATEADD(MONTH, -1, GETDATE()))

answered Mar 29 '12 at 11:24

Mikael Eriksson

136,425
22
210
281

I tried your date you suggested in your comment. It gave the correct output? – Darren Mar 29 '12 at 11:47
2

@DarrenDavies - Check this out and notice the differences of the dates between our answers and the query provided in the question. [End-Of-Month](http://data.stackexchange.com/stackoverflow/query/65491/end-of-month) – Mikael Eriksson Mar 29 '12 at 12:06

SQL datetime needs to read 00:00:00.000

6 Answers6