Solved

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

Posted on 2013-11-13
12
4,444 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

895 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now