Solved

Microsoft SQL Server NOW () function

Posted on 2011-03-15
11
845 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
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 38

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 68

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
 

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 29

Accepted Solution

by:
Paul Jackson earned 250 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 68

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL:  Embedding a CTE 5 32
SQL Exceptions 3 37
Azure SQL Insert not working suddenly 11 21
MSSQL: Substring and Charindex error 7 12
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

911 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

21 Experts available now in Live!

Get 1:1 Help Now