February 12, 2013 at 8:01 am
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:
selecta.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) A socialist is someone who will give you the shirt off *someone else's* back.
February 12, 2013 at 12:06 pm
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:
selecta.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')
February 12, 2013 at 1:01 pm
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)
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:
selecta.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 smalldatetimeset @smalldatetime = GETDATE()
select @smalldatetime
selectLastSundayofMonth =
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) A socialist is someone who will give you the shirt off *someone else's* back.
February 12, 2013 at 1:12 pm
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.
February 12, 2013 at 2:02 pm
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)
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:
selecta.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 smalldatetimeset @smalldatetime = GETDATE()
select @smalldatetime
selectLastSundayofMonth =
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?
February 12, 2013 at 2:32 pm
Michael Valentine Jones (2/12/2013)
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)
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:
selecta.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 smalldatetimeset @smalldatetime = GETDATE()
select @smalldatetime
selectLastSundayofMonth =
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?
If your perception is limited to only this one specific piece of code, then you will never understand what I'm talking about.
I want a general approach that can be used safely in all situations.
Again, I've NEVER EVER had to work with a business date before 1900, and you haven't either, right? So you're accepting potential abends for NO possible gain under any circumstances ever.
Good luck with that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 12, 2013 at 3:20 pm
if date is after 01/01/1900 then following should work
declare @d datetime
set @d='04/29/2013'
select dateadd(day,-(datepart(dw,dateadd(month,datediff(month,-1,@d),-1))-1),
dateadd(month,datediff(month,-1,@d),-1))
February 12, 2013 at 4:35 pm
ScottPletcher (2/12/2013)
If your perception is limited to only this one specific piece of code, then you will never understand what I'm talking about.
I want a general approach that can be used safely in all situations.
Again, I've NEVER EVER had to work with a business date before 1900, and you haven't either, right? So you're accepting potential abends for NO possible gain under any circumstances ever.
Good luck with that.
If your perception is limited to only this one specific piece of code, then you will never understand what I'm talking about.
I have only posted about solutions to the question the OP asked. I cannot respond to imaginary flawed code that is somehow based on my code
"I want a general approach that can be used safely in all situations."
My code produces correct results with all dates, yours does not.
"Again, I've NEVER EVER had to work with a business date before 1900, and you haven't either, right? So you're accepting potential abends for NO possible gain under any circumstances ever."
My code is no more complex that yours, there are no "potential abends" in the code I posted, and it is a general solution that produces correct results with any datetime or smalldatetime value. I have already challenged you to point out the imaginary flaws in my code, but so far: nothing.
If you have some real criticism of my code based on the results of actual queries please post it.
February 13, 2013 at 10:00 am
Any argument that dates before 1900 are unlikely can't really be taken seriously can it? Pure foolishness.
Please put all of the US Presidential elections and their results into a database.
Please put all MLB batting statistics (since records were consistently kept) into a database.
Please put the amendment,s along with ratification dates, to the US Constitution into a database.
February 13, 2013 at 10:14 am
russell-154600 (2/13/2013)
Any argument that dates before 1900 are unlikely can't really be taken seriously can it? Pure foolishness.Please put all of the US Presidential elections and their results into a database.
Please put all MLB batting statistics (since records were consistently kept) into a database.
Please put the amendment,s along with ratification dates, to the US Constitution into a database.
Then 17530107 is pure foolishness too. Please put Caesar's date of death into a db. Please put the date of the Battle of Hastings into a db.
It's absolutely foolish to drastically over-design something to hold values it will NEVER need to hold.
My business doesn't sell things in 1789 and never will.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply