SQL Query to retrieve records that had changed in last 24 hours

Hello,

I have a Microsoft SQL 2012 database that contains records of registration data that I have a big pull that extracts out all information and it works well. But the question I have is, how could I modify it to be able to query a timestamp on the record to only retrieve the records that had changed in the past 24 hours? Thats all that I am being asked for.

I created the view.export that is the collection of all the fields I want including the modified date, but I am not sure the syntax to only pull the past 24 hours. So if it doesnt have anything added in 24 hours, I get nothing back. If for instance my DB name is MyAwesomeDB and my view name is vwe.bestviewever how could I do this?

By the way, I am doing this from Excel as those are the types of reports that I am being asked for.  

Thanks
LVL 2
ademboAsked:
Who is Participating?
 
Tony303Connect With a Mentor Commented:
SELECT *
FROM vwe.bestviewever
WHERE yourdatefield > DATEADD(Hour,-24,GETDATE())
0
 
ademboAuthor Commented:
Sure, but I put it in Access as a query for SQL should be pretty much the same as Access, wouldn't it?
0
 
dsackerConnect With a Mentor Contract ERP Admin/ConsultantCommented:
You would probably add something like this in your WHERE section:

AND  YoutTimestampField >= GETDATE() - 1

The "- 1" subtracts a day. There are other ways to do it, if you prefer a little readability:

AND  YoutTimestampField >= DATEADD(day, -1, GETDATE())

If you like seeing exact hours:

AND  YoutTimestampField >= DATEADD(hour, -24, GETDATE())
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Brian CroweConnect With a Mentor Database AdministratorCommented:
The SQL query would look something like...

SELECT ...
FROM myTable
WHERE myDateColumn > DATEADD(DAY, -1, GETDATE())
0
 
mbizupCommented:
I'll leave Access as is, but add Excel...

<< Access as a query for SQL should be pretty much the same as Access, wouldn't it >>

Depends on where you are running the query from.  Some Access responses will not work.

For example, this is what I would suggest as an Access query - The syntax is a bit different, and it might not fly in your environment:

SELECT *
FROM YourTable
WHERE YourTimeStamp >=  DateAdd ("d" , -1, Date())
0
 
ademboAuthor Commented:
OK, my apologies. I made some assumptions as I'm fairly new to the database world.

Thanks
0
 
mbizupCommented:
No worries -- Give the query in my last post a try too.  It *might* work, but I'm not sure.  I just wanted to make sure that your question was seen by people who are more familiar with querying SQL Server from Excel.
0
 
dsackerContract ERP Admin/ConsultantCommented:
If your database is SQL Server, my examples above will work.
0
 
ademboAuthor Commented:
Thanks All, Im trying out the queries and will respond shortly.
0
 
Brendt HessSenior DBACommented:
There is only one requirement assumed by the above - that you have a datetime field that is updated every time an edit is made.  If this is not true, then you will need to add such a field to the table, and be certain that it is updated with the current date and time whenever the record is updated (and, possibly when the record is added).
0
 
ademboAuthor Commented:
Great point bhess1, and fortunately there is a modified date field already there.

I was able to pull the data with most of what was offered here. Since they all were correct, I can split up the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.