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

LVL 1
davecocksAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this shall do it:
SELECT     COUNT(boatID) + 1 AS Expr1
FROM         diveDetails
WHERE     stamp >= CONVERT(datetime, convert(varchar(10), GETDATE(), 120), 120)
  AND stamp < CONVERT(datetime, convert(varchar(10), dateadd(day, 1, GETDATE()), 120), 120)

Open in new window

0
 
davecocksAuthor Commented:
Perfect!!!
Many Thanks!!
0
 
lucius_theCommented:
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)
---
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
lucius_theCommented:
...loose the second 'WHERE', it's a mistake
0
 
davecocksAuthor Commented:
Hi lucious, thanks for your input. You must have posted after I awarded points. Thanks for your explanation and solution.
0
 
lucius_theCommented:
No problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.