Click here to Skip to main content
15,892,697 members
Search within:


sql query to retrieve data from database based on current date ?
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
ASP.NET
sql query to retrieve data from database based on current date ?
Posted
Add a Solution
Comments
Hasiya 12-Oct-12 7:35am    
SELECT * From tblphoneasign WHERE receivedTime>= dateadd(hh,datediff(hh,0,getdate()),0) is working but this one using method it's nt working??
Hasiya 12-Oct-12 7:36am    
public DataTable newphone()
{
dt.Clear();
dt = SQLTrans.executeQuery("SELECT * From tblphoneasign WHERE receivedTime>= dateadd(hh,datediff(hh,0,getdate()),0)", "tbl1");
return dt;
}
MY method nt working?
[no name] 12-Oct-12 7:51am    
Okay.... read what it is that you wrote and then ask yourself.... "Do you really have something in your database that was received after the current date and time?". Do you really receive items in the future?

2 solutions

Looking at your examples, I'm not sure why you are getting so complicated. To find out the records that are in the future, you would do so like this:
SQL
SELECT *
FROM tblPhoneAsign
WHERE receivedTime > GETDATE()

If you are concerned about only having the date and not the time, you could so something like this:
SQL
SELECT *
FROM tblPhoneAsign
WHERE receivedTime > CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)

That will give you only the date portion of today's date. Doing the adding of zero hours to the GETDATE method is a very complex and costly way of doing things.

As for why the method doesn't work when you call it from code, I would try the code I listed above. If that doesn't work, try knocking off the WHERE statement entirely and seeing if anything comes back. If it doesn't, you know it isn't the fault of the query but instead it is your code. Maybe your connection string is wrong.
 
Share this answer
 
Posted
Comments
fjdiewornncalwe 12-Oct-12 10:25am    
+5. Clean and simple.
Nelek 12-Oct-12 11:00am    
Agree with Marcus. +5
Maciej Los 12-Oct-12 11:39am    
Nice, +5!
But i can't agree with this portion of answer: Doing the adding of zero hours to the GETDATE method is a very complex and costly way of doing things. Why? See my answer.
Tim's answer is very good...

MS SQL Server accept many date and time formats: http://msdn.microsoft.com/en-us/library/ms186724.aspx[^] depends on its version.

So, adding a time part to existing date field should not be a necessary very complex and costly.
In MS SQL Server 2012, it is possible to use DATEFROMPART[^] function, which returns only date value without time part. In the previous version of MS SQL it is possible to write custom function[^].

Finally, to convert datetime field with "00:00:00.000" as time part, it's possible to use a query like this:
SQL
SET DATEFORMAT ymd;
SELECT *
FROM tblPhoneAsign
WHERE receivedTime > CONVERT(DATETIME,CONVERT(NVARCHAR(10),GETDATE(),121))
 
Share this answer
 
Posted
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
Query to retrieve data based on data
sql query for Retrieving based on date and month from two tables.
sql query for retrieving data from date to before 10 dates .
retrieve data from database query
sql query retrieving between two dates.
How to retrieve data based on Date
Retrieve data from a table Using Current Date?
How to wite select query get data from last date to current dates with gap based on partid ?
How to retrieve current month date values in sql server
Retrieving the data from the database based on the parameter

Advertise
Privacy
Cookies
Terms of Use
Last Updated 12 Oct 2012
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web04 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900