GetDate not working

Hi,
I have a very simple query as follows. When I run, it returns no records. I
know there are records that should be in th result. If I insert today's
date instead of getdate() I get all the records for today's date. Getdate()
works if I use > or < instead of =. Any idea why is this behavior? Or is
there another way of accomplishing this.
Select COURSE_NBR as ItemValue
FROM ED_COURSE_CL_1
Where CLASS_DATE = GETDATE()Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
you think match the exact point in time when you run the query? The way to
do this is to use a range query, as you have already discovered. >= {date}
AND < {date + 1} ...
A
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records.
> I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date.
> Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||It is because datetime include both a date and a time portion. See
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Shan" <Shan@.discussions.microsoft.com> wrote in message
news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> Hi,
> I have a very simple query as follows. When I run, it returns no records. I
> know there are records that should be in th result. If I insert today's
> date instead of getdate() I get all the records for today's date. Getdate()
> works if I use > or < instead of =. Any idea why is this behavior? Or is
> there another way of accomplishing this.
> Select COURSE_NBR as ItemValue
> FROM ED_COURSE_CL_1
> Where CLASS_DATE = GETDATE()
>|||Aaron,
Select getdate() is working good and it returns todays date. For today's
date in my query should return one record, which I can verify it by inserting
today's date instead of getdate(). How can I build a range query to only get
records with today's date?
Thanks
"Aaron Bertrand [SQL Server MVP]" wrote:
> Did you look at SELECT GETDATE() ? It is "working" just fine. Does it look
> like just a date? Notice how it has HH:MM:SS.mmm as well. How many rows do
> you think match the exact point in time when you run the query? The way to
> do this is to use a range query, as you have already discovered. >= {date}
> AND < {date + 1} ...
> A
>
> "Shan" <Shan@.discussions.microsoft.com> wrote in message
> news:C0202406-5C2E-481C-B605-A3206B776161@.microsoft.com...
> > Hi,
> > I have a very simple query as follows. When I run, it returns no records.
> > I
> > know there are records that should be in th result. If I insert today's
> > date instead of getdate() I get all the records for today's date.
> > Getdate()
> > works if I use > or < instead of =. Any idea why is this behavior? Or is
> > there another way of accomplishing this.
> >
> > Select COURSE_NBR as ItemValue
> > FROM ED_COURSE_CL_1
> > Where CLASS_DATE = GETDATE()
> >
> >
>
>|||> Select getdate() is working good and it returns todays date.
Correction : it returns today's date AND TIME.
> How can I build a range query to only get
> records with today's date?
DECLARE @.today SMALLDATETIME;
SET @.today = DATEDIFF(DAY, 0, GETDATE());
SELECT
...
WHERE DateColumn >= @.today
AND DateColumn < (@.today + 1);|||Thanks Aaron it's working great.
Cheers!!!
"Aaron Bertrand [SQL Server MVP]" wrote:
> > Select getdate() is working good and it returns todays date.
> Correction : it returns today's date AND TIME.
> > How can I build a range query to only get
> > records with today's date?
> DECLARE @.today SMALLDATETIME;
> SET @.today = DATEDIFF(DAY, 0, GETDATE());
> SELECT
> ...
> WHERE DateColumn >= @.today
> AND DateColumn < (@.today + 1);
>
>
>