Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Last Sunday of a month in sql Expand / Collapse
Author
Message
Posted Monday, February 11, 2013 4:09 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1418679
Posted Monday, February 11, 2013 5:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,631, Visits: 5,281
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!
Post #1418693
Posted Monday, February 11, 2013 5:42 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:50 PM
Points: 3,135, Visits: 11,482
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


Post #1418695
Posted Monday, February 11, 2013 5:53 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:50 PM
Points: 3,135, Visits: 11,482
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)

Post #1418704
Posted Monday, February 11, 2013 7:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:30 PM
Points: 3,631, Visits: 5,281
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!
Post #1418712
Posted Tuesday, February 12, 2013 8:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1418985
Posted Tuesday, February 12, 2013 12:06 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:50 PM
Points: 3,135, Visits: 11,482
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')

Post #1419141
Posted Tuesday, February 12, 2013 1:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:13 AM
Points: 2,044, Visits: 3,060
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1419164
Posted Tuesday, February 12, 2013 1:12 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 16, 2013 8:28 AM
Points: 249, 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.

Post #1419168
Posted Tuesday, February 12, 2013 2:02 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 2:50 PM
Points: 3,135, Visits: 11,482
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?


Post #1419180
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse