Solved

Microsoft SQL Server NOW () function

Posted on 2011-03-15
11
844 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
Comment Utility
Getdate() will be now
0
 
LVL 29

Expert Comment

by:Paul Jackson
Comment Utility

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

Expert Comment

by:Qlemo
Comment Utility
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
Comment Utility
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
Comment Utility
It should be Select id, name, address, Created_Dt
From table A
Where Created_Dt > DateAdd(n,-30, GetDate())
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 29

Accepted Solution

by:
Paul Jackson earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hmm good point Olemo re my DateDiff answer, got carried away with trying to refine it.
0
 

Author Comment

by:supreeths84
Comment Utility
@ Qlemo: I have accepted the answer.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

772 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