• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 438
  • Last Modified:

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

0
davecocks
Asked:
davecocks
  • 3
  • 2
1 Solution
 
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now