Offset Date in a Dataset using Parameter

Question

Offset Date in a Dataset using Parameter

ImYourDBA

SSCertifiable

Points: 5654
More actions
September 14, 2010 at 9:21 am

#227773

How would I do an offset date on a parameter in a dataset? (i.e. - getdate()-365)
My query would look something like this:
select count(distinct ID) as IDcount, cast(floor(cast(showdate as float)) as datetime) as showdate
from table1 where cast(floor(cast(showdate as float)) as datetime) between (@BeginDate)-365 and (@EndDate)-365
group by cast(floor(cast(showdate as float)) as datetime)
order by cast(floor(cast(showdate as float)) as datetime) desc
This obviously gives me an arithmetic error. My end goal is to create a comparison report that will show count of ID from user entered time period compared against same time period last year.
Any ideas?
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

SQLJocky (9/14/2010)
How would I do an offset date on a parameter in a dataset? (i.e. - getdate()-365)
My query would look something like this:
select count(distinct ID) as IDcount, cast(floor(cast(showdate as float)) as datetime) as showdate
from table1 where cast(floor(cast(showdate as float)) as datetime) between (@BeginDate)-365 and (@EndDate)-365
group by cast(floor(cast(showdate as float)) as datetime)
order by cast(floor(cast(showdate as float)) as datetime) desc
This obviously gives me an arithmetic error. My end goal is to create a comparison report that will show count of ID from user entered time period compared against same time period last year.
Any ideas?

1. Use the date functions, and don't convert the dates to ints.

DECLARE @BeginDate datetime,

@EndDate datetime;

-- remove any times from these dates. Subtract # of days

SET @BeginDate = DateAdd(day, DateDiff(day, 0, @BeginDate)-365, 0);

SET @EndDate = DateAdd(day, DateDiff(day, 0, @EndDate)-365, 0);

WITH CTE AS

(

SELECT ID, DateAdd(day, DateDiff(day, 0, GetDate()), 0) as showdate

FROM table1

)

SELECT Count(distinct ID) AS IDCount, showdate

FROM CTE

WHERE showdate between @BeginDate and @EndDate

GROUP BY showdate

ORDER BY showdate DESC;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ImYourDBA SSCertifiable Points: 5654 More actions · Answer 2

Ah, I see now. Thank you!

Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/