DATEDIFF Statement help!

Question

Post reply

DATEDIFF Statement help!

joey6401c

Old Hand

Points: 301
More actions
January 6, 2014 at 6:59 am

#302761

Hi,

I'm trying to use a DATEDIFF statement so I only see data from the last 7 days

How would I write that if I don't have a time stamp in a table to reference?

If I could reference something I know I could do

Where DATEDIFF (dd, example.tabletime, getdate ()) < 7

I need to do it without referencing a value in a table and not sure how

Viewing 10 posts - 1 through 9 (of 9 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

If you haven't got a ~~timestamp~~ date/time column in your table, how can you tell if something is more than 7 days old?

Does your table have an assocoation with any other tables containing dates?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Mitesh Oswal SSCrazy Points: 2798 More actions · Answer 2

Mitesh Oswal

SSCrazy

Points: 2798

January 6, 2014 at 7:29 am

#1677746

I am using the Date from the getdate and used below where clause to find out the last 7 days data



DECLARE @dt Date 

SET @dt = GETDATE()

where  searchdate Between @dt-7 and @dt

Regards,
Mitesh OSwal
+918698619998

joey6401c Old Hand Points: 301 More actions · Answer 3

Thanks for the reply

I modified my query but it returns an error

Delcare @dt date

Set @dt = GETDATE()

select distinct v1.name 'Machine Name', v1. 'Username', t1.displayname 'Unlicensed Application', t1.installdate 'Install Date'

from Inv_AddRemoveProgram t1

left outer join vcomputer v1

on t1._resourceguid = v1.guid

where searchdate between @dt-7 and @dt

ERROR: Must declare the scalar variable "@dt"

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

joey6401c (1/6/2014)

Thanks for the reply

I modified my query but it returns an error

Delcare @dt date

Set @dt = GETDATE()

select distinct v1.name 'Machine Name', v1. 'Username', t1.displayname 'Unlicensed Application', t1.installdate 'Install Date'

from Inv_AddRemoveProgram t1

left outer join vcomputer v1

on t1._resourceguid = v1.guid

where searchdate between @dt-7 and @dt

ERROR: Must declare the scalar variable "@dt"

DECLARE @dt date

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

joey6401c (1/6/2014)

Thanks for the reply

I modified my query but it returns an error

Delcare @dt date

Set @dt = GETDATE()

select distinct v1.name 'Machine Name', v1. 'Username', t1.displayname 'Unlicensed Application', t1.installdate 'Install Date'

from Inv_AddRemoveProgram t1

left outer join vcomputer v1

on t1._resourceguid = v1.guid

where searchdate between @dt-7 and @dt

ERROR: Must declare the scalar variable "@dt"

Is SearchDate a column in your table? If not, that won't work.

Don't just copy code from someone else without understanding what it does (or in this case doesn't) do. What Mitesh posted was an example of how you filter a resultset based on a column in your table.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

ChrisM@Work (1/6/2014)

If you haven't got a ~~timestamp~~ date/time column in your table, how can you tell if something is more than 7 days old?

Does your table have an assocoation with any other tables containing dates?

That probably means that you're using SQL Server 2005 or the database is in the 2005 compatability mode because the DATE datatype isn't available there. You should have also gotten an error that DATE isn't a valid datatype, in this case.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

joey6401c Old Hand Points: 301 More actions · Answer 7

I didn't know if searchdate was a statement I could use

I wrote in my original post that I need to write it without referencing a timestamp in a table

I know I could do it somehow with getdate() just not sure how to write it correctly

I want to basically say where DATEDIFF < GETDATE()-7

This doesn't work but I was thinking something along these lines

Ed Wagner SSC Guru Points: 287015 More actions · Answer 8

You can calculate the date that is 7 days before today, but that won't help you limit your query results because you don't have a date field to compare it against. To limit the rows returned based on date, you need to know when each row was created. Without a date column in the table, I see now way to return the most recent n days worth of rows.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

joey6401c Old Hand Points: 301 More actions · Answer 9

Thanks for your help. I ended up writing it a different way without using dates because I realized that specifying dates wouldn't help with what I was trying to do anyway. I basically wrote it excluding what already exists and once a week after it runs and I get my results I'm going to add the output as additional exclusions. Unfortunately it will require minimal weekly maintenance but that's ok.

Thanks again!