How to calculate campdate > todays date + 45 days in sql query

Question

_{Tuesday, October 23, 2012 1:48 PM}

Hi All;

How to calculate campdate > todays date + 45 days in sql query

below is mu function which is not working

dateadd(day,-45,campdate) > GETDATE()

Any help much appreciated
Thanks

Pradnya07

Pradnya07

All replies (14)

_{Tuesday, October 23, 2012 3:29 PM ✅Answered | 1 vote}

try :

declare @test table(id int ,Name varchar(10),campdate varchar(20),Campaign varchar(20))

insert into @test values (1,'Abc','28/09/2012 10:00','Campaign1')
insert into @test values (1,'Abc','28/10/2012 11:00','Campaign2')
insert into @test values (1,'xyz','28/08/2012 10:00','Campaign1')
insert into @test values (4,'lmn','01/11/2013 09:30','Campaign3')

select *
from @test 

select *
from @test 
where convert(date,convert(varchar(10),convert(date, campdate,103),101),101)   between GETDATE() and  DATEADD(day,45,GETDATE())

_{Wednesday, October 24, 2012 12:50 PM ✅Answered}

In SQL Server 2005 you may use this trick to get rid of time portion

declare @Today datetime, @NextDate datetime

set @Today = dateadd(day, datediff(day,'19000101',CURRENT_TIMESTAMP),'19000101')

set @NextDate = dateadd(day, 46, @Today)


select ....
where CampDate >= @Today and CampDate < @NextDate

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Tuesday, October 23, 2012 1:55 PM}

are you checking if your campdate is greater than (getdate + 45days ) ?? then 

declare @test table(campdate date)

insert into @test values ('2013-01-01')
insert into @test values ('2012-10-23')

select * from @test 
where campdate> DATEADD(day,45,GETDATE())

_{Tuesday, October 23, 2012 1:59 PM}

Thanks for your reply

But its not working

i wonder because the camp date is in  format 2012-11-15 10:00:00.000

Any help much appreciated

Thanks

Pradnya07

_{Tuesday, October 23, 2012 2:03 PM}

Can you tell us the logic little more clearly , or provide sample input and output you expect .

try :

declare @test table(campdate datetime)

insert into @test values ('2013-01-01 20:00:00.000')
insert into @test values ('2012-11-15 10:00:00.000')

select * from @test 
where campdate> DATEADD(day,45,GETDATE())

output :

campdate
2013-01-01 20:00:00.000

_{Tuesday, October 23, 2012 2:08 PM}

select * from @test

where campdate>  CONVERT(VARCHAR(10), Dateadd(day, 45,
Getdate()),121)+ ' 00:00:00.000'--Try this

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Miss Never Giveup .

_{Tuesday, October 23, 2012 2:15 PM}

Pls try

SELECT  * FROM youTable WHERE campdate>DATEADD(DAY, 0, DATEDIFF(DAY, 0, DATEADD(d,45,GETDATE())))

vt

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

_{Tuesday, October 23, 2012 2:26 PM}

Hi All;

Thanks for all your reply

But i think i didnt explain my question clearly

Below is my table

with name , campdate and Campaign( course)

I need all the names of the clients who will be going on a campaign from today to next 45 days only

so my output with names of the client will be

Abc,28/10/2012  11:00:00, Campaign2

Qwe,01/11/2012  12:00:00, Campaign 3

Lmn,01/11/2012  09:30:00, Campaign 3

Any help much appreciated

Thanks

Pradnya07

_{Tuesday, October 23, 2012 7:57 PM | 1 vote}

Assuming you're using SQL Server 2008 and up:

where CampDate >= CAST(CURRENT_TIMESTAMP as Date) and CampDate < CAST(dateadd(day, 46, CURRENT_TIMESTAMP) AS DATE)

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Tuesday, October 23, 2012 8:04 PM}

what is the datatype of your campdate column!! 

_{Wednesday, October 24, 2012 12:24 AM}

try this one that includes hours to count days.

Select [Name],CampDate,Campain
From ta
Where datediff(dd,getdate(),CampDate) between 0 and  45 

_{Wednesday, October 24, 2012 8:00 AM}

Thanks a load

It worked like a charm

Pradnya07

_{Wednesday, October 24, 2012 11:46 AM}

Did you check my solution? It is a sargable solution and it's much simpler.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

_{Wednesday, October 24, 2012 12:47 PM}

I am using SQl server 2005 but still i wil check and let you know

Pradnya07