Compare dates

[Casey]

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?

 

[Adam Machanic]

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.

 

[Aaron Bertrand [SQL Server MVP]]

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

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)

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()))

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

 

[Casey]

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