Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Microsoft SQL Server NOW () function

Posted on 2011-03-15
11
Medium Priority
?
873 Views
Last Modified: 2012-05-11
I have a requirement where I have to fetch all the records from a table which was created 30 minutes from now. Now being the present time. I have a column created_Dt which is of type datetime.

Ex: Table A with fields int id, varchar name, varchar address, datetime Created_Dt. I need to fetch all the values from table A which was created 30 minutes from now.
0
Comment
Question by:supreeths84
[X]
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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 39

Expert Comment

by:Aaron Tomosky
ID: 35143135
Getdate() will be now
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35143170

Select id, name, address, Created_Dt
From table A
Where Created_Dt = DateAdd(n,-30, GetDate())
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35143206
In above dateAdd I would use "mi" instead of "n" - for readability reasons.

Do you need "exact 30 minutes"? Or more like "between now-30 min and now"? With "exact 30 min", we should remove the seconds, or allow for one minute difference.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:supreeths84
ID: 35143210
jacko72: I am not seeing any record. The Created_Dt for a record was 3/15/2011 3:58:23 PM. Yet when I use the query specified by you, I am not seeing that record. Do we need to use DateAdd(n, -30, GetDate()) or something else instead of n
0
 

Author Comment

by:supreeths84
ID: 35143228
It should be Select id, name, address, Created_Dt
From table A
Where Created_Dt > DateAdd(n,-30, GetDate())
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 1000 total points
ID: 35143242
Probably >= would be better if I am guessing your requirements correctly, this will return all records created in the last 30 minutes
Select id, name, address, Created_Dt
From table A
Where Created_Dt >= DateAdd(n,-30, GetDate())
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35143261
In fact even better would be :

Select id, name, address, Created_Dt
From table A
Where DateDiff(n, DateAdd(n,-30, GetDate()), Created_Dt) <= 30
0
 
LVL 71

Expert Comment

by:Qlemo
ID: 35143294
jacko,

No, the last post is no good, performance-wise. DateAdd(mi, -30, GetDate()) ist treated as constant scalar expression, and simple index range scan on created_dt will be performed (if indexed).

Using the column in a function renders any index useless.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35143340
Hmm good point Olemo re my DateDiff answer, got carried away with trying to refine it.
0
 

Author Comment

by:supreeths84
ID: 35168874
@ Qlemo: I have accepted the answer.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

636 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