asked on

Date Difference Query

Hi,

I am using MSSQL 2005
In a database table I have 'datefield' say 'DateSubmited' in table 'Table1'

Now in parameter if age = 2 is passed then I must get the records with date difference of 2

if in Parameter 4 is passed then I must get the records with date difference of 4

For example

If 2 is passed in the parameter

Declare @ageing int

set @ageing = 2

select * from Table1 where DateSubmited has date difference of 2 from today

Thanks in Advance

Binuth

try this

Declare @ageing int
set @ageing = 2
 
 
select * from Table1 where cast(convert(varchar,DateSubmited,101) as datetime) = cast(convert(varchar,DATEADD(day,-@ageing,GETDATE()),101) as datetime)

Open in new window

tia_kamakshi

ASKER

Hi,

Thanks I will check and come back to you in 1 hr

Thanks again

tia_kamakshi

ASKER

Hi,

I have tested the given query, it is giving only one result
exec getDateDiff 2

But I wish multiple result
I have created a procedure where I have used the query.

You can see and procedure code here

If I run the procedure

exec getDateDiff 2
I should get the result (Means gives days -2)
2008-11-06 17:13:16.120
2008-11-04 17:13:16.120
2008-11-02 17:13:16.120
2008-10-31 17:13:16.120
2008-10-29 17:13:16.120
2008-10-27 17:13:16.120
2008-10-25 17:13:16.120
2008-10-23 17:13:16.120
2008-10-21 17:13:16.120
2008-10-19 17:13:16.120
2008-10-17 17:13:16.120
2008-10-15 17:13:16.120

if i run exec getDateDiff 3
then result should be

2008-11-06 17:13:16.120
2008-11-03 17:13:16.120
2008-10-31 17:13:16.120
2008-10-27 17:13:16.120
2008-10-23 17:13:16.120
2008-10-19 17:13:16.120
2008-10-15 17:13:16.120

Create procedure getDateDiff @intAge int
as
 
DECLARE @DateTable Table(
 
dateEntered datetime
 
)
 
 
insert into @DateTable select getdate()
insert into @DateTable select getdate() -1
insert into @DateTable select getdate() -2
insert into @DateTable select getdate() -3
insert into @DateTable select getdate() -4
insert into @DateTable select getdate() -5
insert into @DateTable select getdate() -6
insert into @DateTable select getdate() -7
insert into @DateTable select getdate() -8
insert into @DateTable select getdate() -9
insert into @DateTable select getdate() -10
insert into @DateTable select getdate() -11
 
insert into @DateTable select getdate() -12
insert into @DateTable select getdate() -13
insert into @DateTable select getdate() -14
insert into @DateTable select getdate() -15
insert into @DateTable select getdate() -16
insert into @DateTable select getdate() -17
insert into @DateTable select getdate() -18
insert into @DateTable select getdate() -19
insert into @DateTable select getdate() -20
insert into @DateTable select getdate() -21
insert into @DateTable select getdate() -22
 
select top 100 * from @DateTable 
where 
cast(convert(varchar,dateEntered,101) as datetime) = cast(convert(varchar,DATEADD(day,-@intAge,GETDATE()),101) as datetime)
 
order by dateEntered desc
 
 
 
go
 
exec getDateDiff 2

Open in new window

Binuth

hmm... sorry actually I misread your query first time

try now....

alter procedure getDateDiff @intAge int
as
 
DECLARE @DateTable Table(
 
dateEntered datetime
 
)
 
 
insert into @DateTable select getdate()
insert into @DateTable select getdate() -1
insert into @DateTable select getdate() -2
insert into @DateTable select getdate() -3
insert into @DateTable select getdate() -4
insert into @DateTable select getdate() -5
insert into @DateTable select getdate() -6
insert into @DateTable select getdate() -7
insert into @DateTable select getdate() -8
insert into @DateTable select getdate() -9
insert into @DateTable select getdate() -10
insert into @DateTable select getdate() -11
 
insert into @DateTable select getdate() -12
insert into @DateTable select getdate() -13
insert into @DateTable select getdate() -14
insert into @DateTable select getdate() -15
insert into @DateTable select getdate() -16
insert into @DateTable select getdate() -17
insert into @DateTable select getdate() -18
insert into @DateTable select getdate() -19
insert into @DateTable select getdate() -20
insert into @DateTable select getdate() -21
insert into @DateTable select getdate() -22;
 
 
WITH tst(dateEntered) AS 
(
    SELECT dateEntered
    FROM @DateTable
    WHERE cast(convert(varchar,GETDATE(),101) as datetime) = cast(convert(varchar,dateEntered,101) as datetime)
    UNION ALL
    SELECT d.dateEntered
    FROM @DateTable d
	inner join tst t on cast(convert(varchar,d.dateEntered,101) as datetime) = cast(convert(varchar,DATEADD(day,-@intAge,t.dateEntered),101) as datetime)
)
SELECT *
FROM tst
 
 
 
 
go

Open in new window

tia_kamakshi

ASKER

Many Thanks it works great.

But why it is throwing exception when I pass age = -1
I mean
exec getDateDiff 0

Then I get Exception:

Msg 530, Level 16, State 1, Procedure getDateDiff, Line 37
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

ASKER CERTIFIED SOLUTION

Binuth

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sharath S

There is an unnecesary alais in Binuth's solution. Removed the alias.
Question - This script will produce dates in last 22 days. Is that ok?

alter procedure getDateDiff @intAge int
as
 
DECLARE @DateTable Table(
 
dateEntered datetime
 
)
 
insert into @DateTable select getdate()
insert into @DateTable select getdate() -1
insert into @DateTable select getdate() -2
insert into @DateTable select getdate() -3
insert into @DateTable select getdate() -4
insert into @DateTable select getdate() -5
insert into @DateTable select getdate() -6
insert into @DateTable select getdate() -7
insert into @DateTable select getdate() -8
insert into @DateTable select getdate() -9
insert into @DateTable select getdate() -10
insert into @DateTable select getdate() -11
insert into @DateTable select getdate() -12
insert into @DateTable select getdate() -13
insert into @DateTable select getdate() -14
insert into @DateTable select getdate() -15
insert into @DateTable select getdate() -16
insert into @DateTable select getdate() -17
insert into @DateTable select getdate() -18
insert into @DateTable select getdate() -19
insert into @DateTable select getdate() -20
insert into @DateTable select getdate() -21
insert into @DateTable select getdate() -22;
 
if @intAge > 0
begin
 
WITH tst(dateEntered) AS 
(
    SELECT dateEntered
    FROM @DateTable
    WHERE cast(convert(varchar,GETDATE(),101) as datetime) = cast(convert(varchar,dateEntered,101) as datetime)
    UNION ALL
    SELECT d.dateEntered
    FROM @DateTable d
        inner join tst t on cast(convert(varchar,d.dateEntered,101) as datetime) = cast(convert(varchar,DATEADD(day,-@intAge,t.dateEntered),101) as datetime)
)
SELECT *
FROM tst
 
end
 
else
begin
 
SELECT dateEntered
    FROM @DateTable
 
end

Open in new window

tia_kamakshi

ASKER

Many Thanks