LAst date's for past 3 months

Question

Post reply

LAst date's for past 3 months

sharonsql2013

SSCertifiable

Points: 5908
More actions
February 23, 2016 at 12:59 pm

#399764

How can I show last days for past 3 months say..
Jan 2016 (2016-01-31 23:59:59.000)
, Dec 2015
and Nov. 2015
Need to be dynamic as it will change with current date.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

You mean something like this? You'd need a Calendar table

SELECT *

FROM Calendar

WHERE TheDate>=GETDATE()-90;

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 2

Something like this SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) .

But how to get for Dec and Nov?

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

sharonsql2013 (2/23/2016)
How can I show last days for past 3 months say..
Jan 2016 (2016-01-31 23:59:59.000)
, Dec 2015
and Nov. 2015
Need to be dynamic as it will change with current date.

hi....you say "How can I show last days for past 3 month".....do you mean to use this in a query by limiting results for past 3 months"

or are you trying to build a table of dates for the past three months

...or something else?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Try to understand how this routines work and you'll get the correct formula for your problem.

http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/

I'll explain the first one and you can get the rest.

select dateadd(dd, datediff(dd, 0, @ThisDate), 0)

--It adds the days from '1900-01-01' (date zero) to the defined date (could use GETDATE)

--and then adds them to the date zero.

Tip: It's easier to get the start of the last 3 months including the current month and then substract one second.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

sharonsql2013 (2/23/2016)
Something like this SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) .
But how to get for Dec and Nov?

Use a numbers table or build one on the fly or adjust as necessary.

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

FROM (VALUES(0),(1),(2))x(n)

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 6

sharonsql2013

SSCertifiable

Points: 5908

February 23, 2016 at 1:21 pm

#1860554

Just past 3 months. I am not building a table

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 7

sharonsql2013

SSCertifiable

Points: 5908

February 23, 2016 at 1:22 pm

#1860555

That helps.

Thanks

drew.allen SSC Guru Points: 76985 More actions · Answer 8

sharonsql2013 (2/23/2016)
How can I show last days for past 3 months say..
Jan 2016 (2016-01-31 23:59:59.000)
, Dec 2015
and Nov. 2015
Need to be dynamic as it will change with current date.

I do have to ask why you chose 23:59:59.000 rather than 23:59:59.997. The standard way of handling times is to have the beginning closed (that is "greater than or equal") and the end open (that is "less than"), but if you're going to have both ends closed, you should use the maximum precision allowed for the data type that you are working with. In the case of DATETIME that is 3 milliseconds.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

sharonsql2013 (2/23/2016)
Just past 3 months. I am not building a table

Two way street here, Sharon... please post the code you ended up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 10

This helped me..

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

FROM (VALUES(0),(1),(2))x(n)

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

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

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

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

MMartin1 One Orange Chip Points: 27795 More actions · Answer 11

sharonsql2013 (2/23/2016)
How can I show last days for past 3 months say..
Jan 2016 (2016-01-31 23:59:59.000)
, Dec 2015
and Nov. 2015
Need to be dynamic as it will change with current date.

You mention the "days" so I am not sure if you really need the time portion with 0 milliseconds?

Something like this?

WITH myCTE as (

select dateadd(d, -1, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as myDate

UNION ALL

select dateadd(m, -1, myDate)

from myCTE

WHERE mydate >=dateadd(d, -1, DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0))

)

SELECT * from myCTE

----------------------------------------------------

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

MMartin1 (2/24/2016)
sharonsql2013 (2/23/2016)
How can I show last days for past 3 months say..
Jan 2016 (2016-01-31 23:59:59.000)
, Dec 2015
and Nov. 2015
Need to be dynamic as it will change with current date.
You mention the "days" so I am not sure if you really need the time portion with 0 milliseconds?
Something like this?
WITH myCTE as (
select dateadd(d, -1, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as myDate
UNION ALL
select dateadd(m, -1, myDate)
from myCTE
WHERE mydate >=dateadd(d, -1, DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0))
)
SELECT * from myCTE

Just a reminder, Martin... That's an "Incremental rCTE" and is a form of "Hidden RBAR" and a form of "Slow Death by SQL" even for small numbers of rows. Please see the following article for more information on that subject.

http://www.sqlservercentral.com/articles/T-SQL/74118/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 13

sharonsql2013 (2/24/2016)
This helped me..
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-n,0))
FROM (VALUES(0),(1),(2))x(n)
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0))
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-2,0))

That's what I was afraid of. A second less than the end of the day isn't the end of the day. I'm not sure what you're going to use this for but you really shouldn't try to calculate the end of any given day. Rather, you should calculate the beginning of the next day an make your criteria use "<" for end of day comparisons. For example...

SELECT columnlist

FROM dbo.SomeTable

WHERE somedatecolumn >= @StartDate

AND somedatecolumn < DATEADD(dd,1,@EndDate)

;

... will ensure that you don't miss any entries no matter what the precision of the somedatecolumn is.

For your "on-the-fly micro-calendar-table" you have listed above, I'd recommend the following to support my example above.

SELECT MonthStart = DATEADD(mm,DATEDIFF(mm, 0,GETDATE())-t.N,0)

,NextMonthStart = DATEADD(mm,DATEDIFF(mm,-1,GETDATE())-t.N,0)

FROM (VALUES(0),(1),(2))t(N)

;

If you want help with the real thing, though, you're going to have to post the real code you ended up with. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MMartin1 One Orange Chip Points: 27795 More actions · Answer 14

Jeff Moden (2/24/2016)
MMartin1 (2/24/2016)
sharonsql2013 (2/23/2016)
How can I show last days for past 3 months say..
Jan 2016 (2016-01-31 23:59:59.000)
, Dec 2015
and Nov. 2015
Need to be dynamic as it will change with current date.
You mention the "days" so I am not sure if you really need the time portion with 0 milliseconds?
Something like this?
WITH myCTE as (
select dateadd(d, -1, DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) as myDate
UNION ALL
select dateadd(m, -1, myDate)
from myCTE
WHERE mydate >=dateadd(d, -1, DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0))
)
SELECT * from myCTE
Just a reminder, Martin... That's an "Incremental rCTE" and is a form of "Hidden RBAR" and a form of "Slow Death by SQL" even for small numbers of rows. Please see the following article for more information on that subject.
http://www.sqlservercentral.com/articles/T-SQL/74118/

Even for a small number of rows? Interesting. I will take a look at the article, thank you Jeff.

Of course I was mainly focused why the time portion -1 second from midnight. Is the goal to list the dates at face value or to use them in a date range filter? I think its the latter. In that case use the >= and < is the way to go.

Jan data gotten by way of -- >

myDate >='2016-01-01' and myDate < '2016-02-01' -- Not <=2016-01-31 23:59:59.000

----------------------------------------------------