asked on

using Getdate() in SPROC

Hi,

table: diveDetails
fields: boatID int
stamp datetime

I'm trying to get a (count() add 1) of all the records that contain 'todays' date.

I'm guessing the below SQL 'works' but isn't returning anything as the date contains time as well.

my date stamp is like this: 22/02/2009 14:22
and I guess the GETDATE is similar i.e. 22/02/2009 16:15

Is it possible in the SQL to return stamp and GetDate as just the 'date'. I've tried using the CONVERT() statement but can't get it to work

Thanks

AS
	
SELECT     COUNT(boatID) + 1 AS Expr1
FROM         diveDetails
WHERE     (stamp = GETDATE())
 
RETURN
 
 
AS
	
SELECT     COUNT(boatID) + 1 AS Expr1
FROM         diveDetails
WHERE     (CONVERT (date, stamp) = (CONVERT (date, GETDATE())
RETURN

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davecocks

ASKER

Perfect!!!
Many Thanks!!

lucius_the

the explanation is that when you compare datetime values in T-SQL you will not get a hit for two any two datetime values that have the same date, because time is also contained in the datetime value. So you have to convert datetime values first to a format that does not contain the time (or sets the time to zeroes)

Do this both for the value that Getdate() function returns and the value that you want to compare in the database. Then the comparison works as you expected.

Try using
---
WHERE CAST(CONVERT(CHAR(10),stamp,101) AS SMALLDATETIME) = WHERE CAST(CONVERT(CHAR(10),getdate(),101) AS SMALLDATETIME)
---

lucius_the

...loose the second 'WHERE', it's a mistake

davecocks

ASKER

Hi lucious, thanks for your input. You must have posted after I awarded points. Thanks for your explanation and solution.

lucius_the

No problem