February 9, 2013 at 3:48 pm
Hi all,
I would like to know how to find last sunday of a month in sql... I wanted basically to find last sunday of month between feb and august in sql...
Any help on this?
Thanks
February 9, 2013 at 4:35 pm
Find the last day of the month, and then find the Sunday on or before that date.
select
a.DT,
LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1),
LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107')
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())
) a
order by
a.DT
Results:
DT LastDayofMonth LastSundayofMonth
----------------------- ----------------------- -----------------------
2012-12-09 18:30:40.447 2012-12-31 00:00:00.000 2012-12-30 00:00:00.000
2013-01-09 18:30:40.447 2013-01-31 00:00:00.000 2013-01-27 00:00:00.000
2013-02-09 18:30:40.447 2013-02-28 00:00:00.000 2013-02-24 00:00:00.000
2013-03-09 18:30:40.447 2013-03-31 00:00:00.000 2013-03-31 00:00:00.000
2013-04-09 18:30:40.447 2013-04-30 00:00:00.000 2013-04-28 00:00:00.000
2013-05-09 18:30:40.447 2013-05-31 00:00:00.000 2013-05-26 00:00:00.000
2013-06-09 18:30:40.447 2013-06-30 00:00:00.000 2013-06-30 00:00:00.000
2013-07-09 18:30:40.447 2013-07-31 00:00:00.000 2013-07-28 00:00:00.000
2013-08-09 18:30:40.447 2013-08-31 00:00:00.000 2013-08-25 00:00:00.000
2013-09-09 18:30:40.447 2013-09-30 00:00:00.000 2013-09-29 00:00:00.000
2013-10-09 18:30:40.447 2013-10-31 00:00:00.000 2013-10-27 00:00:00.000
2013-11-09 18:30:40.447 2013-11-30 00:00:00.000 2013-11-24 00:00:00.000
2013-12-09 18:30:40.447 2013-12-31 00:00:00.000 2013-12-29 00:00:00.000
2014-01-09 18:30:40.447 2014-01-31 00:00:00.000 2014-01-26 00:00:00.000
Start of Week Function:
February 11, 2013 at 2:59 am
If you don't like magic numbers, I think you can do it this way too:
select
a.DT,
LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1),
LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107'),
DwainsWay =
1+dateadd(mm,datediff(mm,-1,a.DT),-1)-DATEPART(weekday, dateadd(mm,datediff(mm,-1,a.DT),-1))
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())
) a
order by
a.DT
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2013 at 3:30 am
Thanks all for your reply....Shall try and let you know guys!
February 11, 2013 at 7:45 am
dwain.c (2/11/2013)
If you don't like magic numbers, I think you can do it this way too:
select
a.DT,
LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1),
LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107'),
DwainsWay =
1+dateadd(mm,datediff(mm,-1,a.DT),-1)-DATEPART(weekday, dateadd(mm,datediff(mm,-1,a.DT),-1))
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())
) a
order by
a.DT
Your code, "DwainsWay", is sensitive to the setting of DATEFIRST and to the setting for language.
You can see what happens if you put either of these before your code.
I don't see how you code eliminates "magic numbers", since it uses the same -1 (Date 18991231) as my code.
set datefirst 4
set language 'spanish'
February 11, 2013 at 8:50 am
Method below works for any and all date and language settings:
DECLARE @startDate datetime
DECLARE @number_of_months int
SET @startDate = GETDATE()
SET @number_of_months = 7
SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_month
FROM (
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1 + month_offset, 0)) AS last_day_of_month
FROM (
SELECT 0 AS month_offset UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) AS month_offsets
WHERE
month_offset BETWEEN 0 AND (@number_of_months - 1)
) AS derived
ORDER BY
1
Edit: It's not a "magic" number, of course, just a known Sunday. Then the only "assumption" needed for the code to work is every 7 days after that Sunday it will be Sunday again, which is about as safe an assumption as it gets :-).
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".
February 11, 2013 at 9:17 am
ScottPletcher (2/11/2013)
Method below works for any and all date and language settings:
DECLARE @startDate datetime
DECLARE @number_of_months int
SET @startDate = GETDATE()
SET @number_of_months = 7
SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_month
FROM (
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1 + month_offset, 0)) AS last_day_of_month
FROM (
SELECT 0 AS month_offset UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) AS month_offsets
WHERE
month_offset BETWEEN 0 AND (@number_of_months - 1)
) AS derived
ORDER BY
1
Edit: It's not a "magic" number, of course, just a known Sunday. Then the only "assumption" needed for the code to work is every 7 days after that Sunday it will be Sunday again, which is about as safe an assumption as it gets :-).
An issue with your code is that it does not work with dates before 1900-01-06.
That is why I used '17530107' in my code, which is really similar to your code, except that the only issue is with dates before '17530107' for which there is no non-null solution when you use SQL Server datetime.
For example, try the code with this:
SET @startDate = '18470228'
February 11, 2013 at 9:48 am
[superceded by next post]
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".
February 11, 2013 at 9:49 am
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
Method below works for any and all date and language settings:
DECLARE @startDate datetime
DECLARE @number_of_months int
SET @startDate = GETDATE()
SET @number_of_months = 7
SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', last_day_of_month) / 7 * 7, '19000107') AS last_sunday_of_month
FROM (
SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @startDate) + 1 + month_offset, 0)) AS last_day_of_month
FROM (
SELECT 0 AS month_offset UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL
SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
) AS month_offsets
WHERE
month_offset BETWEEN 0 AND (@number_of_months - 1)
) AS derived
ORDER BY
1
Edit: It's not a "magic" number, of course, just a known Sunday. Then the only "assumption" needed for the code to work is every 7 days after that Sunday it will be Sunday again, which is about as safe an assumption as it gets :-).
An issue with your code is that it does not work with dates before 1900-01-06.
That is why I used '17530107' in my code, which is really similar to your code, except that the only issue is with dates before '17530107' for which there is no non-null solution when you use SQL Server datetime.
For example, try the code with this:
SET @startDate = '18470228'
I haven't been in any business that worked with dates before 1900, but if yours did, then that would be a relevant concern for you. Besides, we frequently use smalldatetime here, so no dates before 1900 would be valid anyway. I'm thinking the odds of a smalldatetime being used are vastly greater for most people than needing a date before 1900.
The other difference being that I didn't hard-code the number of months, of course.
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".
February 11, 2013 at 3:21 pm
ScottPletcher (2/11/2013)
ScottPletcher (2/11/2013)
...
I haven't been in any business that worked with dates before 1900, but if yours did, then that would be a relevant concern for you. Besides, we frequently use smalldatetime here, so no dates before 1900 would be valid anyway. I'm thinking the odds of a smalldatetime being used are vastly greater for most people than needing a date before 1900...
It's true that dates before 1900 are rare in most applications, but it's no more work to code '17530107' than to code '19000107', so there is no real advantage to using '19000107' and for a public forum, I prefer the more general solution.
February 11, 2013 at 4:09 pm
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
ScottPletcher (2/11/2013)
...
I haven't been in any business that worked with dates before 1900, but if yours did, then that would be a relevant concern for you. Besides, we frequently use smalldatetime here, so no dates before 1900 would be valid anyway. I'm thinking the odds of a smalldatetime being used are vastly greater for most people than needing a date before 1900...
It's true that dates before 1900 are rare in most applications, but it's no more work to code '17530107' than to code '19000107', so there is no real advantage to using '19000107' and for a public forum, I prefer the more general solution.
Not true: there's one HUGE advantage to 19000101 and later: code with smalldatetimes abend when using dates before 19000101.
So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.
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".
February 11, 2013 at 5:35 pm
Michael Valentine Jones (2/11/2013)
dwain.c (2/11/2013)
If you don't like magic numbers, I think you can do it this way too:
select
a.DT,
LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1),
LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107'),
DwainsWay =
1+dateadd(mm,datediff(mm,-1,a.DT),-1)-DATEPART(weekday, dateadd(mm,datediff(mm,-1,a.DT),-1))
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())
) a
order by
a.DT
Your code, "DwainsWay", is sensitive to the setting of DATEFIRST ...
Indeed this is true that's why I said (with emphasis on think) the following. Sorry for being a bit too short on time to explain in detail.
dwain.c (2/11/2013)
If you don't like magic numbers, I think[/i] you can do it this way
Michael Valentine Jones (2/11/2013)
...and to the setting for language.You can see what happens if you put either of these before your code.
I don't see how you code eliminates "magic numbers", since it uses the same -1 (Date 18991231) as my code.
set datefirst 4
set language 'spanish'
Could you please explain why you think it is sensitive to language? I didn't see anything in testing to support it.
Also, here's a quite general solution based on a Calendar function, that in truth I'd probably be using. This one is sensitive to language.
select
a.DT
,LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1)
,LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107')
,LastSundayUsingCalendarFunction=c.LastSunday
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate()) UNION ALL
SELECT '1753-01-07'
) a
CROSS APPLY dbo.GenerateCalendar(a.Dt, 1) b
CROSS APPLY (
SELECT LastSunday=c.[Date]
FROM dbo.GenerateCalendar(b.LDtOfMo, -7) c
-- Change 'SU' as appropriate to your language setting
WHERE [Last] = 1 AND WkDName2 = 'SU') c
order by
a.DT
Here is the GenerateCalendar FUNCTION.
CREATE FUNCTION [dbo].[GenerateCalendar]
(
@FromDate DATETIME,
@NoDays INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
SELECT [SeqNo] = t.N,
[Date] = dt.DT,
[Year] = dp.YY,
[YrNN] = dp.YY % 100,
[YYYYMM] = dp.YY * 100 + dp.MM,
[BuddhaYr] = dp.YY + 543,
[Month] = dp.MM,
[Day] = dp.DD,
[WkDNo] = DATEPART(dw,dt.DT),
[WkDName] = CONVERT(NCHAR(9),dp.DW),
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
[JulDay] = dp.DY,
[JulWk] = dp.DY/7+1,
[WkNo] = dp.DD/7+1,
[Qtr] = DATEPART(qq,dt.Dt),
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY ( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY, Acourtesy of ChrisM)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 11, 2013 at 5:42 pm
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
ScottPletcher (2/11/2013)
...
I haven't been in any business that worked with dates before 1900, but if yours did, then that would be a relevant concern for you. Besides, we frequently use smalldatetime here, so no dates before 1900 would be valid anyway. I'm thinking the odds of a smalldatetime being used are vastly greater for most people than needing a date before 1900...
It's true that dates before 1900 are rare in most applications, but it's no more work to code '17530107' than to code '19000107', so there is no real advantage to using '19000107' and for a public forum, I prefer the more general solution.
Not true: there's one HUGE advantage to 19000101 and later: code with smalldatetimes abend when using dates before 19000101.
So I'm supposed to use different base dates depending on data type? Nope, not me. I want to be able to change a column from datetime to smalldatetime w/o abending tons of code just from that change.
Did you test that? This seems to work OK for me:
select
a.DT,
LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1),
LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107')
from
( -- Test data
select DT = convert(smalldatetime,getdate())union all
select DT = convert(smalldatetime,dateadd(mm,-2,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,-1,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,1,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,2,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,3,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,4,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,5,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,6,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,7,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,8,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,9,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,10,getdate()))union all
select DT = convert(smalldatetime,dateadd(mm,11,getdate()))
) a
order by
a.DT
Results:
DT LastDayofMonth LastSundayofMonth
----------------------- ----------------------- -----------------------
2012-12-11 19:39:00 2012-12-31 00:00:00.000 2012-12-30 00:00:00.000
2013-01-11 19:39:00 2013-01-31 00:00:00.000 2013-01-27 00:00:00.000
2013-02-11 19:39:00 2013-02-28 00:00:00.000 2013-02-24 00:00:00.000
2013-03-11 19:39:00 2013-03-31 00:00:00.000 2013-03-31 00:00:00.000
2013-04-11 19:39:00 2013-04-30 00:00:00.000 2013-04-28 00:00:00.000
2013-05-11 19:39:00 2013-05-31 00:00:00.000 2013-05-26 00:00:00.000
2013-06-11 19:39:00 2013-06-30 00:00:00.000 2013-06-30 00:00:00.000
2013-07-11 19:39:00 2013-07-31 00:00:00.000 2013-07-28 00:00:00.000
2013-08-11 19:39:00 2013-08-31 00:00:00.000 2013-08-25 00:00:00.000
2013-09-11 19:39:00 2013-09-30 00:00:00.000 2013-09-29 00:00:00.000
2013-10-11 19:39:00 2013-10-31 00:00:00.000 2013-10-27 00:00:00.000
2013-11-11 19:39:00 2013-11-30 00:00:00.000 2013-11-24 00:00:00.000
2013-12-11 19:39:00 2013-12-31 00:00:00.000 2013-12-29 00:00:00.000
2014-01-11 19:39:00 2014-01-31 00:00:00.000 2014-01-26 00:00:00.000
February 11, 2013 at 5:53 pm
dwain.c (2/11/2013)
Michael Valentine Jones (2/11/2013)
dwain.c (2/11/2013)
If you don't like magic numbers, I think you can do it this way too:
select
a.DT,
LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1),
LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107'),
DwainsWay =
1+dateadd(mm,datediff(mm,-1,a.DT),-1)-DATEPART(weekday, dateadd(mm,datediff(mm,-1,a.DT),-1))
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())
) a
order by
a.DT
Your code, "DwainsWay", is sensitive to the setting of DATEFIRST ...
Indeed this is true that's why I said (with emphasis on think) the following. Sorry for being a bit too short on time to explain in detail.
dwain.c (2/11/2013)
If you don't like magic numbers, I think[/i] you can do it this wayMichael Valentine Jones (2/11/2013)
...and to the setting for language.You can see what happens if you put either of these before your code.
I don't see how you code eliminates "magic numbers", since it uses the same -1 (Date 18991231) as my code.
set datefirst 4
set language 'spanish'Could you please explain why you think it is sensitive to language? I didn't see anything in testing to support it.
Here is the test below that I ran to demo the impact of a non-us english setting for language.
set language 'english'
go
set language 'spanish'
go
select
a.DT,
LastDayofMonth = dateadd(mm,datediff(mm,-1,a.DT),-1),
LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7,'17530107'),
DwainsWay =
1+dateadd(mm,datediff(mm,-1,a.DT),-1)-DATEPART(weekday, dateadd(mm,datediff(mm,-1,a.DT),-1))
from
( -- Test data
select DT = getdate()union all
select DT = dateadd(mm,-2,getdate())union all
select DT = dateadd(mm,-1,getdate())union all
select DT = dateadd(mm,1,getdate())union all
select DT = dateadd(mm,2,getdate())union all
select DT = dateadd(mm,3,getdate())union all
select DT = dateadd(mm,4,getdate())union all
select DT = dateadd(mm,5,getdate())union all
select DT = dateadd(mm,6,getdate())union all
select DT = dateadd(mm,7,getdate())union all
select DT = dateadd(mm,8,getdate())union all
select DT = dateadd(mm,9,getdate())union all
select DT = dateadd(mm,10,getdate())union all
select DT = dateadd(mm,11,getdate())
) a
order by
a.DT
Results:
Changed language setting to us_english.
Se cambió la configuración de idioma a Español.
DT LastDayofMonth LastSundayofMonth DwainsWay
----------------------- ----------------------- ----------------------- -----------------------
2012-12-11 19:49:06.253 2012-12-31 00:00:00.000 2012-12-30 00:00:00.000 2012-12-31 00:00:00.000
2013-01-11 19:49:06.253 2013-01-31 00:00:00.000 2013-01-27 00:00:00.000 2013-01-28 00:00:00.000
2013-02-11 19:49:06.253 2013-02-28 00:00:00.000 2013-02-24 00:00:00.000 2013-02-25 00:00:00.000
2013-03-11 19:49:06.253 2013-03-31 00:00:00.000 2013-03-31 00:00:00.000 2013-03-25 00:00:00.000
2013-04-11 19:49:06.253 2013-04-30 00:00:00.000 2013-04-28 00:00:00.000 2013-04-29 00:00:00.000
2013-05-11 19:49:06.253 2013-05-31 00:00:00.000 2013-05-26 00:00:00.000 2013-05-27 00:00:00.000
2013-06-11 19:49:06.253 2013-06-30 00:00:00.000 2013-06-30 00:00:00.000 2013-06-24 00:00:00.000
2013-07-11 19:49:06.253 2013-07-31 00:00:00.000 2013-07-28 00:00:00.000 2013-07-29 00:00:00.000
2013-08-11 19:49:06.253 2013-08-31 00:00:00.000 2013-08-25 00:00:00.000 2013-08-26 00:00:00.000
2013-09-11 19:49:06.253 2013-09-30 00:00:00.000 2013-09-29 00:00:00.000 2013-09-30 00:00:00.000
2013-10-11 19:49:06.253 2013-10-31 00:00:00.000 2013-10-27 00:00:00.000 2013-10-28 00:00:00.000
2013-11-11 19:49:06.253 2013-11-30 00:00:00.000 2013-11-24 00:00:00.000 2013-11-25 00:00:00.000
2013-12-11 19:49:06.253 2013-12-31 00:00:00.000 2013-12-29 00:00:00.000 2013-12-30 00:00:00.000
2014-01-11 19:49:06.253 2014-01-31 00:00:00.000 2014-01-26 00:00:00.000 2014-01-27 00:00:00.000
(14 row(s) affected)
February 11, 2013 at 7:05 pm
Apparently changing the language from english to spanish changes the @@DATEFIRST value from 7 to 1.
I didn't realize that side-effect. Interesting...
It also accounts for why I didn't see it while testing as I was trying to test @@DATEFIRST setting (forcing it) at the same time I was testing the language change.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply