CODEWITHSUNDEEP
sql-server
Sameep Baxi
Sameep Baxi

Reputation: 111

To get the previous month's last date till last second

I want to get last month's last date (whether 30 or 31) and time till last second, whenever the query is executed. Eg. 11/30/2015 11:59:59 PM

So I have a query like 

SELECT DATEADD(ss, (60*60*24)-1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1))

It solves my problem. But what is the difference between the query written above and the one below, when I change the DATEDIFF part and replace 0 with 1?

SELECT DATEADD(ss, (60*60*24)-1, DATEADD(MONTH, DATEDIFF(MONTH, 1, GETDATE()), -1))

Will both of these queries gives the same result whenever they are run, or which should I consider as the permanent solution?

Upvotes: 1

Views: 3312

Answers (5)

Paul Maxwell
Paul Maxwell

Reputation: 35603

Do NOT do this; attempt to get the "last second" of the last day of the previous month

I make this bold statement on the assumption you are attempting to use BETWEEN and you are concerned with the accuracy of something like this:

select sum(value) from Atable
where [Adate] BETWEEN '20151201' AND '21051231 23:59:59'

But the complexity of arriving at the last point in time on the last day of any month is solved so easily by using the first day of the next month instead. All that you also need to do is drop the use of BETWEEN. Like this:

select sum(value) from Atable
where [Adate] >= '20151201' and [Adate] < '21060101'

LESS THAN "the first day of the this month"

That is how you solve your conundrum.

& by the way: The precision (accuracy) of smalldatetime, datetime and datetime2 all differ, all the more reason not to use BETWEEN.

see "Be careful about rounding errors." at http://sqlmag.com/t-sql/t-sql-best-practices-part-2

Specifically, do this:

DateLogged < SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

This will be 100% accurate for date, smalldatetime, datetime and datetime2 columns.

Here is another attempt to explain why LESS THAN [the_next_day_at_00:00:00+0000000] is accurate and using 22:59:59 is NOT accurate. Please take note of the sample data accuracy

SQL Fiddle

MS SQL Server 2014 Schema Setup:

Query 1:

DECLARE @Tbl TABLE
    (  [ID] int identity(1,1)
     , [DT_a] datetime
     , [DT_b] datetime
     , [DT_c] datetime2
    )
 
INSERT INTO @Tbl
    ([Dt_a], [Dt_b], [Dt_c])
VALUES
    (
       '20151231 23:59:59'
     , '20151231 23:59:59.997'
     , '20151231 23:59:59.9999999'
    )

select
      'where [DT_b] <= 20151231 23:59:59' as FilterString
    , max([Dt_a])  as [Dt_a]
    , max([Dt_b])  as [Dt_b]
    , max([Dt_c])  as [Dt_c]
from @Tbl
where [DT_b] <= '20151231 23:59:59'

UNION ALL
select
      'where [DT_b] < 20160101'
    , max([Dt_a])  as [Dt_a]
    , max([Dt_b])  as [Dt_b]
    , max([Dt_c])  as [Dt_c]
from @Tbl
where [DT_b] < '20160101'

Results:

|                      FilterString |                       Dt_a |                       Dt_b |                        Dt_c |
|-----------------------------------|----------------------------|----------------------------|-----------------------------|
| where [DT_b] <= 20151231 23:59:59 |                     (null) |                     (null) |                      (null) |
|           where [DT_b] < 20160101 | December, 31 2015 23:59:59 | December, 31 2015 23:59:59 | 2015-12-31 23:59:59.9999999 |

Data accuracy

To avoid possible errors from rounding by time units DO NOT USE <= 23:59:59

Instead use LESS THAN [the_next_day]

AND, as a consequence AVOID USING BETWEEN for date ranges.

Upvotes: 2

Leonan Vasconcelos
Leonan Vasconcelos

Reputation: 11

To get last second of current month use:

SELECT DATEADD(MILLISECOND, -10, CAST (EOMONTH(GETDATE()) AS DATETIME))

and you get:

2021-12-30 23:59:59.000

Explanation: takes begin of next month (2021-12-31) and convert to datetime (2021-12-31 00:00:00.000) then takes 1 second to get (2021-12-30 23:59:59.000)

Upvotes: 0

lisandro
lisandro

Reputation: 506

Well it would be expected that substracting one millisecond to the first day of the current month you would get the last millisecond of the previous month but it doesn't work that whay, with datediff millisecond,-1 you still get the first day of the month you have to do datediff millisecond,-2 to reach 997 milliseconds, no way to get 999 nor 998.(without using text).

select  dateadd(MILLISECOND,-2,dateadd(month, datediff(month, 0, getdate()), 0))

And you get 2020-01-31 23:59:59.997

Upvotes: 0

bmsqldev
bmsqldev

Reputation: 2735

It is because of DATEDIFF( MONTH, 0, GETDATE()) Function

If you use an integer as the second argument, this is interpreted as the number of days since 1900-01-01 regardless of the Interval you are using in the datediff function.

For eg:

   SELECT YEAR(0), MONTH(0), DAY(0); 

 year    month     day

 1900    1          1

Now if I Increment 0 to 1 in year, month, day

SELECT YEAR(1), MONTH(1), DAY(1); 

 year    month     day

 1900    1          2

Now if I Increment values to 365,

 SELECT YEAR(365), MONTH(365), DAY(365); 

 year      month     day

 1901    1          1

You can see the Year got incremented by 1.

there are many ways to find out the previous month's last date. Here is the one I am using.

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

Upvotes: 0

vmachan
vmachan

Reputation: 1682

See this link for how DATEDIFF is used or should be used in SQL Server. The 2nd argument, the one which does not seem to make a difference in your case, is supposed to be the start date which is subtracted from the end date (getdate()) to get the difference and then converted to months. I would try and use this function the typical way and provide a proper start date.

Also below is an alternative way of getting the same result

SELECT DATEADD(ss, -1, '01/' + CONVERT(VARCHAR, DATEPART(MONTH, getdate())) + '/' + CONVERT(VARCHAR, DATEPART(YEAR, getdate())));

Upvotes: 0

Related Questions