SQL Sproc - date problem with getdate().

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Sproc - date problem with getdate().

Author

Topic

Lorna70
Starting Member

19 Posts

Posted - 2010-10-11 : 03:09:03

Hi I have an sproc as follows:

spGetTodayJobLoads
AS
BEGIN
SELECT * FROM vListLoads
WHERE DateAdded = GETDATE()
ORDER BY DateAdded DESC
END

However, even though I have added records today, this returns no results. Each time I add a record the default for DateAdded (which is a datetime field) is getdate(). A value against one record is 2010-10-11 07:59:33.920. Is the problem because there is a time element and the time doesn't match? If so, how do I handle this to retrieve records which only match the date element of the value?? I'm a SQL newbie so please bear with me :-).

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-10-11 : 03:12:08

"WHERE DateAdded = GETDATE()"

It matches date include time!

You need to try like this

Where convert(varchar(15),DateAdded ,101) =convert(varchar(15),getdate(),101)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-10-11 : 03:33:33

If you have an index on DateAdded then it is better to use the following query:


DECLARE @date DATETIME;
DECLARE @today DATETIME;
DECLARE @tomorrow DATETIME;

SET @date = GETDATE();
SET @today = DATEADD(DAY, DATEDIFF(DAY, 1, @date), 1);
SET @tomorrow = DATEADD(DAY, DATEDIFF(DAY, 0, @date), 1);

SELECT *
  FROM vListLoads
 WHERE DateAdded >= @today
   AND DateAdded < @tomorrow
 ORDER BY DateAdded DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources