SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Last Sunday of a month in sql


Last Sunday of a month in sql

Author
Message
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19613 Visits: 7410
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
[quote]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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17807 Visits: 6431
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 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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14471 Visits: 11848
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
[quote]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


Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14471 Visits: 11848
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 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.





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)


dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17807 Visits: 6431
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19613 Visits: 7410
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
[quote]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







It's inherent in the very definition of a smalldatetime:


declare @smalldatetime smalldatetime
set @smalldatetime = GETDATE()
select @smalldatetime

select DATEDIFF(day, '175301017', @smalldatetime)



Why you are so insistent on placing abend traps throughout your code for something that never happens? I've never worked on an any business item from 1899 or before.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14471 Visits: 11848
ScottPletcher (2/12/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
[quote]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







It's inherent in the very definition of a smalldatetime:


declare @smalldatetime smalldatetime
set @smalldatetime = GETDATE()
select @smalldatetime

select DATEDIFF(day, '175301017', @smalldatetime)



Why you are so insistent on placing abend traps throughout your code for something that never happens? I've never worked on an any business item from 1899 or before.


The code I posted works fine with smalldatetime.

If you can't be bothered to run the simple test script I posted to confirm that, I don't know what else I can do.

Could you at least try to run this?
declare @smalldatetime smalldatetime
set @smalldatetime = GETDATE()
select @smalldatetime

select LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')


ScottPletcher
ScottPletcher
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19613 Visits: 7410
Michael Valentine Jones (2/12/2013)
ScottPletcher (2/12/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
[quote]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







It's inherent in the very definition of a smalldatetime:


declare @smalldatetime smalldatetime
set @smalldatetime = GETDATE()
select @smalldatetime

select DATEDIFF(day, '175301017', @smalldatetime)



Why you are so insistent on placing abend traps throughout your code for something that never happens? I've never worked on an any business item from 1899 or before.


The code I posted works fine with smalldatetime.

If you can't be bothered to run the simple test script I posted to confirm that, I don't know what else I can do.

Could you at least try to run this?
declare @smalldatetime smalldatetime
set @smalldatetime = GETDATE()
select @smalldatetime

select LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')






The one specific example you posted worked fine. Yes, I had a typo.

DATEDIFF is doing some type of implicit conversion. I don't want to rely on that, and it certainly won't be true if, say, I try to set the base date into a column and then do the date functions on it.

Thus, you never know when a smalldatetime is going to abend your code if you try to use '17530701' as a base date. Again, if you insist on adding abend traps into your code for absolutely no valid business reason whatsoever, go right ahead. But I won't do it and I will encourage others not to as well.

SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial:
If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.
DiverKas
DiverKas
Mr or Mrs. 500
Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)Mr or Mrs. 500 (575 reputation)

Group: General Forum Members
Points: 575 Visits: 460
Saw this snippet somewhere and thought it was a good solution:

DECLARE @Year INT

SET @Year =2012

SELECT months,MAX(dates) AS DT_MONTH
FROM (SELECT MONTH(DATEADD(DAY, number-1, DATEADD(YEAR, @Year-1900, 0))) AS MONTHS,
DATEADD(DAY, number-1, DATEADD(YEAR, @Year-1900, 0)) AS DATES
FROM MASTER..spt_values
WHERE type='P'
AND number BETWEEN 1 AND DATEDIFF(DAY, DATEADD(YEAR, @Year-1900, 0), DATEADD(YEAR, @Year-1900+1, 0))) AS T
WHERE DATENAME(WEEKDAY, dates)='Sunday'
GROUP BY months,DATEADD(MONTH, DATEDIFF(MONTH, 0, dates), 0)



Probably covers the 95% of tasks out there.
Michael Valentine Jones
Michael Valentine Jones
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14471 Visits: 11848
ScottPletcher (2/12/2013)
Michael Valentine Jones (2/12/2013)
ScottPletcher (2/12/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
Michael Valentine Jones (2/11/2013)
ScottPletcher (2/11/2013)
[quote]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







It's inherent in the very definition of a smalldatetime:


declare @smalldatetime smalldatetime
set @smalldatetime = GETDATE()
select @smalldatetime

select DATEDIFF(day, '175301017', @smalldatetime)



Why you are so insistent on placing abend traps throughout your code for something that never happens? I've never worked on an any business item from 1899 or before.


The code I posted works fine with smalldatetime.

If you can't be bothered to run the simple test script I posted to confirm that, I don't know what else I can do.

Could you at least try to run this?
declare @smalldatetime smalldatetime
set @smalldatetime = GETDATE()
select @smalldatetime

select LastSundayofMonth =
dateadd(dd,(datediff(dd,'17530107',dateadd(mm,datediff(mm,-1,@smalldatetime),-1))/7)*7,'17530107')






The one specific example you posted worked fine. Yes, I had a typo.

DATEDIFF is doing some type of implicit conversion. I don't want to rely on that, and it certainly won't be true if, say, I try to set the base date into a column and then do the date functions on it.

Thus, you never know when a smalldatetime is going to abend your code if you try to use '17530701' as a base date. Again, if you insist on adding abend traps into your code for absolutely no valid business reason whatsoever, go right ahead. But I won't do it and I will encourage others not to as well.


Well at least we can agree that my code works with all possible datetime or smalldatetime values, and yours only produces valid results with dates after 1900-01-01.

As for the non-existent "abend traps", what can I say until you can actually demo one that impacts my code?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search