Solved

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

Posted on 2013-11-13
12
4,953 Views
Last Modified: 2013-11-13
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
0
Comment
Question by:adembo
  • 4
  • 2
  • 2
  • +3
12 Comments
 
LVL 2

Author Comment

by:adembo
ID: 39645595
Sure, but I put it in Access as a query for SQL should be pretty much the same as Access, wouldn't it?
0
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 167 total points
ID: 39645596
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
 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 166 total points
ID: 39645597
The SQL query would look something like...

SELECT ...
FROM myTable
WHERE myDateColumn > DATEADD(DAY, -1, GETDATE())
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 61

Expert Comment

by:mbizup
ID: 39645615
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
 
LVL 2

Author Comment

by:adembo
ID: 39645629
OK, my apologies. I made some assumptions as I'm fairly new to the database world.

Thanks
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39645647
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
 
LVL 20

Expert Comment

by:dsacker
ID: 39645654
If your database is SQL Server, my examples above will work.
0
 
LVL 12

Accepted Solution

by:
Tony303 earned 167 total points
ID: 39645725
SELECT *
FROM vwe.bestviewever
WHERE yourdatefield > DATEADD(Hour,-24,GETDATE())
0
 
LVL 2

Author Comment

by:adembo
ID: 39645736
Thanks All, Im trying out the queries and will respond shortly.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 39645791
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
 
LVL 2

Author Comment

by:adembo
ID: 39645828
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

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question