help with dateAdd function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help with dateAdd function

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-13 : 06:34:09

Hi,

I have a SPROC that I am attempting to write, where I pass an INT parameter @monthsAgo, like so

@monthsAgo int,
--0 denotes current month so far, 1 = previous full month, 2 = 2 months previous full month etc

In the query I run a "WHERE date BETWEEN xxxx and xxxx"

So if I passed a 0, I would get the between dates as Nov1 2008 and current date at 12am (doesnt bring back todays full day).

If I passed a 1, it would do a BETWEEN Oct1 2008 and Oct 31 2008
If I passed a 2, it would do a BETWEEN Sep1 2008 and Sep 30 2008

Is something like this possible with the DateAdd function ?

Perhaps I should be doing this login in my front end and just passing dynamic dates.

Either way any help is much appreciated..

thanks again!
mike123

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 07:03:47

use date between xxxx and dateadd(dd,1,xxxx)

to include ending dates records also

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-11-13 : 07:04:32

declare @counter int
set @counter=12

--for start date
select cast (floor( cast (dateadd(day,-1* datepart(day,dateadd(month,@counter*-1,getdate())),dateadd(month,@counter*-1,getdate()))+1 as float)) as datetime)

-- for end date
select (CASE WHEN MONTH(dateadd(month,@counter*-1,getdate()))= 12
THEN DATEADD(day, -1, CAST('01/01/' + STR(YEAR(dateadd(month,@counter*-1,getdate()))+1) AS DateTime))
ELSE DATEADD(day, -1, CAST(STR(MONTH(dateadd(month,@counter*-1,getdate()))+1) + '/01/' + STR(YEAR(dateadd(month,@counter*-1,getdate()))) AS DateTime))
END)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 07:18:18

SELECT *
FROM Table1
WHERE colDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - @MonthsAgo, 0)

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-13 : 07:29:04

Ok, I'm feeling I'm missing something here but can't you just use DATEDIFF instead??

->

SELECT * FROM table WHERE DATEDIFF(mm, date, GETDATE()) = @monthsAgo

EDIT: ...or is it a requirement to use the dateadd function?

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 07:32:02

Yes you can, but you also makes it impossible for sql server to use any index (in a godd fashion) over date column.

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 07:45:45

quote:
Originally posted by sakets_2000

declare @counter int
set @counter=12

--for start date
select cast (floor( cast (dateadd(day,-1* datepart(day,dateadd(month,@counter*-1,getdate())),dateadd(month,@counter*-1,getdate()))+1 as float)) as datetime)

-- for end date
select (CASE WHEN MONTH(dateadd(month,@counter*-1,getdate()))= 12
THEN DATEADD(day, -1, CAST('01/01/' + STR(YEAR(dateadd(month,@counter*-1,getdate()))+1) AS DateTime))
ELSE DATEADD(day, -1, CAST(STR(MONTH(dateadd(month,@counter*-1,getdate()))+1) + '/01/' + STR(YEAR(dateadd(month,@counter*-1,getdate()))) AS DateTime))
END)

declare @counter int
set @counter=12

select dateadd(month,@counter-2,dateadd(year,year(getdate())-1900,0))
select dateadd(month,@counter-1,dateadd(year,year(getdate())-1900,0))-1

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 07:54:24

quote:
Originally posted by mike123

Hi,

I have a SPROC that I am attempting to write, where I pass an INT parameter @monthsAgo, like so

@monthsAgo int,
--0 denotes current month so far, 1 = previous full month, 2 = 2 months previous full month etc

In the query I run a "WHERE date BETWEEN xxxx and xxxx"

So if I passed a 0, I would get the between dates as Nov1 2008 and current date at 12am (doesnt bring back todays full day).

If I passed a 1, it would do a BETWEEN Oct1 2008 and Oct 31 2008
If I passed a 2, it would do a BETWEEN Sep1 2008 and Sep 30 2008

Is something like this possible with the DateAdd function ?

Perhaps I should be doing this login in my front end and just passing dynamic dates.

Either way any help is much appreciated..

thanks again!
mike123


declare @month int
set @month=0


select columns from table
where 
datecol>=dateadd(month,datediff(month,@month ,getdate())-@month ,0) and
datecol<dateadd(month,datediff(month,@month ,getdate())-@month +1,0)

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-13 : 08:04:08

I belive this complies with the requirements and is able to use an index. Not very readable though but I believe that it's the only one that complies with the requirements so far

SELECT * 
FROM table
WHERE date BETWEEN 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - @monthsago, 0)
  AND 
  CASE WHEN @monthsago = 0 
    THEN DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - (@monthsago-1), 0)
    END

- Lumbago

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-13 : 08:07:49

quote:
Originally posted by madhivanan

quote:
Originally posted by mike123

Hi,

I have a SPROC that I am attempting to write, where I pass an INT parameter @monthsAgo, like so

@monthsAgo int,
--0 denotes current month so far, 1 = previous full month, 2 = 2 months previous full month etc

In the query I run a "WHERE date BETWEEN xxxx and xxxx"

So if I passed a 0, I would get the between dates as Nov1 2008 and current date at 12am (doesnt bring back todays full day).

If I passed a 1, it would do a BETWEEN Oct1 2008 and Oct 31 2008
If I passed a 2, it would do a BETWEEN Sep1 2008 and Sep 30 2008

Is something like this possible with the DateAdd function ?

Perhaps I should be doing this login in my front end and just passing dynamic dates.

Either way any help is much appreciated..

thanks again!
mike123

declare @month int
set @month=0

select columns from table
where
datecol>=dateadd(month,datediff(month,@month ,getdate())-@month ,0) and
datecol<dateadd(month,datediff(month,@month ,getdate())-@month +1,0)

Madhivanan

Failing to plan is Planning to fail

Hey Madhivanan,

Excellent just tested this one and it appears to be working perfectly.. thanks very much for your help!

thanks again,
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-13 : 08:16:14

quote:
Originally posted by Lumbago

I belive this complies with the requirements and is able to use an index. Not very readable though but I believe that it's the only one that complies with the requirements so far
SELECT *
FROM table
WHERE date BETWEEN
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - @monthsago, 0)
AND
CASE WHEN @monthsago = 0
THEN DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - (@monthsago-1), 0)
END

- Lumbago

Hey Lumbago,

Just came across this after successfully testing the previous post. I wasnt able to satisfy requirements with the other suggested queries either.

After trying yours I found its returning near perfect results in the same amount of time as the solution posted 1 post above yours. For some reason when I pass a "1" as the @monthsAgo parameter, there is some slight overlap. We are getting data from Oct1, and while it should end Oct31 , we are going some seconds into Nov1. I'm getting 115 records which could be related to the 15 seconds it takes to execute? not sure just a guess..

Perhaps this query is essentially the same as Madhivanans?

thanks again!
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-13 : 08:21:25

I think lumbago meant to write an open-ended query, not a between.query.

quote:

SELECT * 
FROM table
WHERE date >=  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - @monthsago, 0)
  and date <   CASE WHEN @monthsago = 0 
    THEN DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - (@monthsago-1), 0)
    END

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-13 : 08:32:13

What do you mean by this?

quote:
we are going some seconds into Nov1.

If @monthsAgo = 1 then query would be BETWEEN '2008-10-01 00:00:00.000' AND '2008-11-01 00:00:00.000' and it's impossible that it returns data who's date are after '2008-11-01 00:00:00.000'. You should however do as Peso says and use >= and < instead of between in this case.

- Lumbago

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-13 : 08:46:35

Hi,

Peso's revision works perfectly too. Its fixed the strange issue I was having with records running into Nov1 , not sure how or why that happened but its not happening anymore.

I'm not sure if Peso's updated method or Madhivanans is more efficient or is better for indexes?, but they seem to be performing the same from what I can see so far.

thanks once again!!
mike123

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-13 : 08:53:34

The only difference is that madhis version will include data for the full current month if @monthsAgo = 0 while mine will only include data up until the start of the current day.

- Lumbago

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-13 : 08:59:41

Hi Lumbago,

Gotcha. Thanks! Actually the "date" is populated with the date of insert, so we won't be looking at future records. So I guess you accomodated for that too which is awesome and good to know. We don't have this condition on this particular query, so they are effectively the same for me ...

Your contribution was very helpful and much appreciated !!

thanks again,
mike123

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-14 : 01:15:24

So finally which one do you want to use?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-14 : 02:03:04

quote:
Originally posted by mike123

Actually the "date" is populated with the date of insert, so we won't be looking at future records.

Then post made 11/13/2008 : 07:18:18 meets your criteria?

E 12°55'05.63"
N 56°04'39.26"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-11-14 : 02:16:43

quote:
Originally posted by Peso

quote:
Originally posted by mike123

Actually the "date" is populated with the date of insert, so we won't be looking at future records.

Then post made 11/13/2008 : 07:18:18 meets your criteria?

E 12°55'05.63"
N 56°04'39.26"

When the current month is @monthsAgo = 0, yes it works perfectly... your updated solutions works perfectly ..

thx again guys, this is now resolved :)

cheers,
mike123

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources