DateAdd Bug 1

[mitsiguri]

Hi All,

shouldn't the following sql:

select dateAdd(m,-1,'5/31/2002')

return '5/01/2002' instead of '4/30/2002

im using sql server 7

cheers
alan

 

[tlbroadbent]

No. SQL handled it properly. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[mitsiguri]

ok,

so is there any way i can make it return '5/01/2002'

cheers
alan

 

[tlbroadbent]

I assume you want to return the first day of a month for a given date. Try this.

Declare @date datetime
Select @date='5/31/2002'
Select FOM=dateadd(d, 1-DAY(@date), @date)

Find the first day of the month (FOM) corresponding to the month of the current system date.

Select FOM=dateadd(d, 1-DAY(getdate()), left(getdate(),11)) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[mitsiguri]

ok,

just to make the problem clearer, heres the sql

SELECT [Date],[No],(dateAdd(m,-1,[Date])) as lastmonth,sales=(select count(*) from WebmasterSales where subid in (select subid from webmastersubsites wsu inner join webmastersites wsi on wsu.siteid = wsi.siteid where webmastername=@WebmasterName and wsu.active=1) and createDateTime >= dateAdd(m,-1,#RebillInfo.[Date]) and createDateTime < dateAdd(m,-1,dateAdd(d,1,#RebillInfo.[Date])))
FROM #RebillInfo

lets say #RebillInfo contains dates from 5/15/2002 to 5/31/2002

the problem is that may has 31 days and april only has 30, so when [Date] = '5/30/2002', sales returns a wrong value
because dateAdd(m,-1,dateAdd(d,1,#RebillInfo.[Date])) returns '4/30/2002'

cheers
alan

 

[tlbroadbent]

You didn't tell me what you want to have instead (Date - 1 month). Do you want the first day of the month? If so, substitute the expression I provided for the expression that is incorrect. If you don't want the FOM then explain what it is that you do want.

Do you also want a different value for LastMonth?

SELECT [Date],
[No],
(dateAdd(m,-1,[Date])) as LastMonth,
Sales=
(select count(*)
from WebmasterSales
where subid in
(select subid
from webmastersubsites wsu
inner join webmastersites wsi
on wsu.siteid = wsi.siteid
where webmastername=@WebmasterName
and wsu.active=1)
and createDateTime >= dateadd(d, 1-DAY([Date]), [Date])
and createDateTime < dateAdd(m,-1,dateAdd(d,1,#RebillInfo.[Date])))
FROM #RebillInfo
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[mitsiguri]

lets say #RebillInfo contains dates and values 5/15/2002 to 5/31/2002:

[Date] [No]
5/15/2002 0
5/16/2002 3
-- etc
5/30/2002 5
5/31/2002 2

this subquery adds another 2 columns, with the date a month before and the number of sales

SELECT [Date],[No],(dateAdd(m,-1,[Date])) as lastmonth,sales=(select count(*) from WebmasterSales where subid in (select subid from webmastersubsites wsu inner join webmastersites wsi on wsu.siteid = wsi.siteid where webmastername=@WebmasterName and wsu.active=1) and createDateTime >= dateAdd(m,-1,#RebillInfo.[Date]) and createDateTime < dateAdd(m,-1,dateAdd(d,1,#RebillInfo.[Date])))
FROM #RebillInfo

however, when #RebillInfo.[Date] contains '5/30/2002' , dateAdd(m,-1,dateAdd(d,1,#RebillInfo.[Date])) returns '4/30/2002'

what i want it to return is '5/1/2002' as i want to search between 4/30/2002 & 5/1/2002

the problem is caused i guess by the fact that may has 31 days and april only has 30

thank for the help tlbroadbent!

cheers
alan

 

[tlbroadbent]

I don't know how to help if you won't use the suggestions I've already made. I understand the problem and have proposed a solution. Have you tried my solution? Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[genomon]

This is pretty basic, but your SQL, &quot;select dateAdd(m,-1,'5/31/2002')&quot;, uses &quot;m&quot; for the function, meaning you want the operation performed on Months, not days. Since April contains only 30 days, one month was decremented by the function as it should be. KIS!

 

[mitsiguri]

sorry, dont have my head screwed on right today, thanks for the help