Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2013-11-13
12
4,780 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

829 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