Solved

Microsoft SQL Server NOW () function

Posted on 2011-03-15
11
849 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 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 69

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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 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 69

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

860 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