tia_kamakshi
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
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
ASKER
Hi,
Thanks I will check and come back to you in 1 hr
Thanks again
Thanks I will check and come back to you in 1 hr
Thanks again
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
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
hmm... sorry actually I misread your query first time
try now....
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
Many Thanks
Open in new window