• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 876
  • Last Modified:

Microsoft SQL Server NOW () function

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
supreeths84
Asked:
supreeths84
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Aaron TomoskyTechnology ConsultantCommented:
Getdate() will be now
0
 
Paul JacksonCommented:

Select id, name, address, Created_Dt
From table A
Where Created_Dt = DateAdd(n,-30, GetDate())
0
 
QlemoC++ DeveloperCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
supreeths84Author Commented:
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
 
supreeths84Author Commented:
It should be Select id, name, address, Created_Dt
From table A
Where Created_Dt > DateAdd(n,-30, GetDate())
0
 
Paul JacksonCommented:
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
 
Paul JacksonCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
Paul JacksonCommented:
Hmm good point Olemo re my DateDiff answer, got carried away with trying to refine it.
0
 
supreeths84Author Commented:
@ Qlemo: I have accepted the answer.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now