Compare dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi!
I'm just attempting to write a SQL statement that will see if a date matches.
Using asp.net, I place the date into a field in the database. It's formatted
like this:
08/12/2005

I'd like to create a query that counts how many rows a date (yesterday) is in.
This is the query I created:

select count(*) from FSRTurnover where theDate = Convert(Char(12),
DateAdd("d", -1, getdate()), 101)

but it doesn't seem to work. It doesn't return any rows...
If I use a static date, it does work:

select count(*) from FSRTurnover where theDate = '08/18/2005'

Any ideas why this might be?
 
A couple of issues here.

First of all, 8/12/2005 is an ambiguous date. Depending on locale it might
mean August 12 or December 8. To be safe, always use the ISO date format,
YYYYMMDD.

Second, your query is only good for dates that happen to have a timestamp of
exactly midnight. But I'm guessing that's not what you really want... You
probably want ALL times from yesterday?

Try:

select count(*)
from FSRTurnover
where theDate >= DATEADD(dd, DATEDIFF(dd, 1, GETDATE()), 0)
AND theDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

That will give you all dates >= midnight yesterday, and < midnight today.
 
I'm just attempting to write a SQL statement that will see if a date
matches.
Using asp.net, I place the date into a field in the database. It's
formatted
like this:
08/12/2005
Click to expand...

No, it's not, if it is a DATETIME or SMALLDATETIME column. That is just how
*your* client tool shows it to you. Behind the scenes, it is actually
stored as two numeric values and does not have any ridiculously ambiguous
and confusing format like mm/dd/yyyy.
I'd like to create a query that counts how many rows a date (yesterday) is
in.
This is the query I created:

select count(*) from FSRTurnover where theDate = Convert(Char(12),
DateAdd("d", -1, getdate()), 101)
Click to expand...

Why are you converting to a character format? And why on earth would you
use CHAR(12)? You're comparing dates, not strings!

SELECT COUNT(*)
FROM FSRTurnover
WHERE theDate -- awful column name!
= DATEADD(DAY,-1,DATEDIFF(DAY,0,GETDATE()))
Click to expand...
AND theDate
< DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))

or broken down:

DECLARE @yesterday SMALLDATETIME, @today SMALLDATETIME
SET @yesterday = DATEDIFF(DAY, 0, GETDATE())-1
SET @today = @yesterday + 1

SELECT COUNT(*)
FROM FSRTurnover
WHERE theDate >= @yesterday
AND theDate < @today

A
 
Yeah. That seemed to work. I keep the ISO date format thing in mind for the
future.
Thanks.
 
Back
Top