Date Problem!

A column in a SQL Server7 DB table has the datatype smalldatetime. All
the records in this column are stored in 'm/d/yyyy' format like
6/15/2005 (15th June 2005), 8/21/2005 (21st August 2005) etc....
Assume that two of the records stores today's date i.e. 9/20/2005 (20th
September 2005). When I execute the following 2 queries in QA
SELECT * FROM tblRecords WHERE Date1='9/20/2005'
SELECT * FROM tblRecords WHERE Date1='20050920'
both the queries retrieve the two records as expected but when I issue
the following query
SELECT * FROM tblRecords WHERE Date1=GETDATE()
no records get retrieved! Why so?
Thanks,
ArpanBecause GETDATE() includes time information. Did you run SELECT GETDATE()
and see that it does not return exactly 20050920?
If you want all rows from today, the most efficient way is probably:
WHERE Date1 >= 0 + DATEDIFF(DAY, 0, GETDATE())
AND Date1 < 1 + DATEDIFF(DAY, 0, GETDATE())
or
DECLARE @.today SMALLDATETIME
SET @.today = 0 + DATEDIFF(DAY, 0, GETDATE())
SELECT
...
WHERE Date1 >= @.today
AND Date1 < (@.today + 1)
(In addition to using an index on date1, this will also allow you to ignore
any time values that might slip into the values in date1.)
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1127189510.583307.210400@.f14g2000cwb.googlegroups.com...
>A column in a SQL Server7 DB table has the datatype smalldatetime. All
> the records in this column are stored in 'm/d/yyyy' format like
> 6/15/2005 (15th June 2005), 8/21/2005 (21st August 2005) etc....
> Assume that two of the records stores today's date i.e. 9/20/2005 (20th
> September 2005). When I execute the following 2 queries in QA
> SELECT * FROM tblRecords WHERE Date1='9/20/2005'
> SELECT * FROM tblRecords WHERE Date1='20050920'
> both the queries retrieve the two records as expected but when I issue
> the following query
> SELECT * FROM tblRecords WHERE Date1=GETDATE()
> no records get retrieved! Why so?
> Thanks,
> Arpan
>|||Thanks, Aaron, for your help. Good suggestion :-)
Thanks once again,
Regards,
Arpan