February 23, 2016 at 12:59 pm
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.
February 23, 2016 at 1:02 pm
You mean something like this? You'd need a Calendar table
SELECT *
FROM Calendar
WHERE TheDate>=GETDATE()-90;
February 23, 2016 at 1:07 pm
Something like this SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) .
But how to get for Dec and Nov?
February 23, 2016 at 1:08 pm
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
February 23, 2016 at 1:09 pm
Try to understand how this routines work and you'll get the correct formula for your problem.
http://qa.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.
February 23, 2016 at 1:12 pm
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)
February 23, 2016 at 1:21 pm
Just past 3 months. I am not building a table
February 23, 2016 at 1:22 pm
That helps.
Thanks
February 23, 2016 at 2:58 pm
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
February 23, 2016 at 3:07 pm
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
February 24, 2016 at 9:47 am
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))
February 24, 2016 at 5:21 pm
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
----------------------------------------------------
February 24, 2016 at 6:36 pm
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://qa.sqlservercentral.com/articles/T-SQL/74118/
--Jeff Moden
February 24, 2016 at 6:48 pm
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
February 25, 2016 at 11:38 am
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.
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
----------------------------------------------------
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply