Last Sunday of a month in sql

Rate Topic

Display Mode

Topic Options

Author

Message

ScottPletcher

Posted Monday, February 11, 2013 4:09 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

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)
One man with courage makes a majority. Andrew Jackson

Post #1418679

dwain.c

Posted Monday, February 11, 2013 5:35 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,345, Visits: 3,191

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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1418693

Michael Valentine Jones

Posted Monday, February 11, 2013 5:42 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517

ScottPletcher (2/11/2013)

Michael Valentine Jones (2/11/2013)

ScottPletcher (2/11/2013)

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

Michael Valentine Jones

Posted Monday, February 11, 2013 5:53 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517

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)

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

dwain.c

Posted Monday, February 11, 2013 7:05 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:03 AM
Points: 2,345, Visits: 3,191

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.

Post #1418712

ScottPletcher

Posted Tuesday, February 12, 2013 8:01 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

Michael Valentine Jones (2/11/2013)

ScottPletcher (2/11/2013)

Michael Valentine Jones (2/11/2013)

ScottPletcher (2/11/2013)

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)
One man with courage makes a majority. Andrew Jackson

Post #1418985

Michael Valentine Jones

Posted Tuesday, February 12, 2013 12:06 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517

ScottPletcher (2/12/2013)

Michael Valentine Jones (2/11/2013)

ScottPletcher (2/11/2013)

Michael Valentine Jones (2/11/2013)

ScottPletcher (2/11/2013)

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

ScottPletcher

Posted Tuesday, February 12, 2013 1:01 PM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778

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)

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.

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)
One man with courage makes a majority. Andrew Jackson

Post #1419164

DiverKas

Posted Tuesday, February 12, 2013 1:12 PM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 10:24 AM
Points: 237, Visits: 413

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

Michael Valentine Jones

Posted Tuesday, February 12, 2013 2:02 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 10:43 AM
Points: 2,945, Visits: 10,517

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)

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.

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')

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 »