ScottPletcher (2/12/2013)

---

Michael Valentine Jones (2/11/2013)

---

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

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

selectLastSundayofMonth =

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