Compare dates

12 Aug 2005 5:32 PM

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?

12 Aug 2005 5:42 PM

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.

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Show quote

"Casey" <Ca***@discussions.microsoft.com> wrote in message
news:D31A0999-5F58-4882-A0B5-C910A7F813C0@microsoft.com...
> 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?

12 Aug 2005 6:05 PM

Casey

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

Show quote

"Adam Machanic" wrote:

> 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.
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
> "Casey" <Ca***@discussions.microsoft.com> wrote in message
> news:D31A0999-5F58-4882-A0B5-C910A7F813C0@microsoft.com...
> > 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?
>
>
>

12 Aug 2005 5:43 PM

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