• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 510
  • Last Modified:

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
0
tia_kamakshi
Asked:
tia_kamakshi
  • 4
  • 3
1 Solution
 
BinuthCommented:
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

0
 
tia_kamakshiAuthor Commented:
Hi,

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

Thanks again
0
 
tia_kamakshiAuthor Commented:
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

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
BinuthCommented:
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

0
 
tia_kamakshiAuthor Commented:
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.
0
 
BinuthCommented:
hmm... you need to validate like...

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 d.dateEntered
    FROM @DateTable

end

0
 
SharathData EngineerCommented:

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

0
 
tia_kamakshiAuthor Commented:
Many Thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now