March 28, 2016 at 12:24 pm
I wrote the query and it returned the results only on the 28th of each month.
With cteMonths
as (
select DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())-12, 0)) AS ReportMonth, row_number() over( order by DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as num
union all
select DATEADD(mm, num, DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())-12, 0))), num+1
from cteMonths
where num < 13)
SELECT ReportMonth from cteMonths
The result that I had was:
2015-02-28 23:59:59.000
2015-03-28 23:59:59.000
2015-04-28 23:59:59.000
2015-05-28 23:59:59.000
2015-06-28 23:59:59.000
2015-07-28 23:59:59.000
2015-08-28 23:59:59.000
2015-09-28 23:59:59.000
2015-10-28 23:59:59.000
2015-11-28 23:59:59.000
2015-12-28 23:59:59.000
2016-01-28 23:59:59.000
2016-02-28 23:59:59.000
--------------------
How can I really get the actual last day of each month, with the time of 23:59:59.000 ?
We will be using the cte, as shown in the query. Please do not declare any other variables.
So, the results should appear as follow:
2015-02-28 23:59:59.000
2015-03-31 23:59:59.000
2015-04-30 23:59:59.000
2015-05-31 23:59:59.000
2015-06-30 23:59:59.000
2015-07-31 23:59:59.000
2015-08-31 23:59:59.000
2015-09-30 23:59:59.000
2015-10-31 23:59:59.000
2015-11-30 23:59:59.000
2015-12-31 23:59:59.000
2016-01-31 23:59:59.000
2016-02-29 23:59:59.000
Thanks.
March 28, 2016 at 12:35 pm
This will work, but I'd ask what your intentions are for this as I personally would not use the dates for a close-ended query on date ranges.
with qTally as (
select
n = row_number() over (order by (select null)) - 1
from
(values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)
), EOM13 as (
select
CalendarDate = dateadd(second, -1, dateadd(month,datediff(month,0,getdate()) - n, 0))
from
qTally
)
select * from EOM13 order by CalendarDate desc;
March 28, 2016 at 12:42 pm
Thank you very much.
March 28, 2016 at 1:04 pm
23:59:59.000 does not really make sense in SQL Server, since it doesn't match any system data type's time sensitivity.
Smalldatetime is 23:59 and datetime is 23:59:59.997.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 28, 2016 at 1:30 pm
ScottPletcher (3/28/2016)
23:59:59.000 does not really make sense in SQL Server, since it doesn't match any system data type's time sensitivity.Smalldatetime is 23:59 and datetime is 23:59:59.997.
Or datetime2 where it is 23:59:59.9999999.
Obviously ignored the rest of my comment about the use of the data.
March 28, 2016 at 2:46 pm
Tamrak (3/28/2016)
I wrote the query and it returned the results only on the 28th of each month.
With cteMonths
as (
select DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())-12, 0)) AS ReportMonth, row_number() over( order by DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) as num
union all
select DATEADD(mm, num, DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())-12, 0))), num+1
from cteMonths
where num < 13)
SELECT ReportMonth from cteMonths
The result that I had was:
2015-02-28 23:59:59.000
2015-03-28 23:59:59.000
2015-04-28 23:59:59.000
2015-05-28 23:59:59.000
2015-06-28 23:59:59.000
2015-07-28 23:59:59.000
2015-08-28 23:59:59.000
2015-09-28 23:59:59.000
2015-10-28 23:59:59.000
2015-11-28 23:59:59.000
2015-12-28 23:59:59.000
2016-01-28 23:59:59.000
2016-02-28 23:59:59.000
--------------------
How can I really get the actual last day of each month, with the time of 23:59:59.000 ?
We will be using the cte, as shown in the query. Please do not declare any other variables.
So, the results should appear as follow:
2015-02-28 23:59:59.000
2015-03-31 23:59:59.000
2015-04-30 23:59:59.000
2015-05-31 23:59:59.000
2015-06-30 23:59:59.000
2015-07-31 23:59:59.000
2015-08-31 23:59:59.000
2015-09-30 23:59:59.000
2015-10-31 23:59:59.000
2015-11-30 23:59:59.000
2015-12-31 23:59:59.000
2016-01-31 23:59:59.000
2016-02-29 23:59:59.000
Thanks.
This is "Death by SQL" because so much can happen in that last second of the day. You really need to use a WHERE clause that looks like this...
WHERE SomeDateTimeColumn >= SomeMonthStart
AND SomeDateTimeColumn < NextMonthStart
;
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2016 at 2:49 pm
Tamrak (3/28/2016)
Thank you very much.
Do you actually understand HOW it works?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2016 at 2:54 pm
Jeff Moden (3/28/2016)
Tamrak (3/28/2016)
Thank you very much.Do you actually understand HOW it works?
Probably not, but then he hasn't told us how he will be using it either.
March 28, 2016 at 3:07 pm
Lynn Pettis (3/28/2016)
Jeff Moden (3/28/2016)
Tamrak (3/28/2016)
Thank you very much.Do you actually understand HOW it works?
Probably not, but then he hasn't told us how he will be using it either.
That's why I normally don't post an answer until I'm sure the OP isn't trying in earnest to set themselves on fire. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2016 at 3:59 pm
Jeff Moden (3/28/2016)
Lynn Pettis (3/28/2016)
Jeff Moden (3/28/2016)
Tamrak (3/28/2016)
Thank you very much.Do you actually understand HOW it works?
Probably not, but then he hasn't told us how he will be using it either.
That's why I normally don't post an answer until I'm sure the OP isn't trying in earnest to set themselves on fire. 😀
Well, someone else would have answered and who knows what kind of answer that might have been. 😉
March 29, 2016 at 7:28 am
with qTally as (
select
n = row_number() over (order by (select null)) - 1
from
(values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)
), EOM13 as (
select
CalendarDate = dateadd(second, -1, dateadd(month,datediff(month,0,getdate()) - n, 0))
from
qTally
)
select * from EOM13 order by CalendarDate desc;
Could you please explain what the dt(n) is used for? Thanks
March 29, 2016 at 7:48 am
alicesql (3/29/2016)
with qTally as (
select
n = row_number() over (order by (select null)) - 1
from
(values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)
), EOM13 as (
select
CalendarDate = dateadd(second, -1, dateadd(month,datediff(month,0,getdate()) - n, 0))
from
qTally
)
select * from EOM13 order by CalendarDate desc;
Could you please explain what the dt(n) is used for? Thanks
This (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) is a derived table and needs to be aliased. The dt (derived table) the alias I chose and the (n) is used to name the column as n.
Run the following and look at the results:
select
n,
n1 = row_number() over (order by (select null)) - 1
from
(values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n);
March 29, 2016 at 7:52 am
Simplified my code:
with qTally as (
select
n
from
(values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))dt(n)
), EOM13 as (
select
CalendarDate = dateadd(second, -1, dateadd(month,datediff(month,0,getdate()) - n, 0))
from
qTally
)
select * from EOM13 order by CalendarDate desc;
March 29, 2016 at 9:08 pm
Please explain why you want the last second of the month. Is it because you think that's the way to get everything for a given month? If not, then why? I ask because it could really make a difference in what's actually needed by you.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply