Filter Date

I got a field called Date#, datatype = datetime, default value = getdate().

So after i insert a record, the Date# will auto record the system date and the time. And my MS SQL Server show: 6/9/2004 12:24:35 PM

However i am not able to filter my record by date. The statement i tried are:

SELECT * from mytable WHERE Date# = '6/9/2004 12:24:35 PM'

SELECT * from mytable WHERE Date# = "6/9/2004 12:24:35 PM"

SELECT * from mytable WHERE Date# = 6/9/2004 12:24:35 PM

SELECT * from mytable WHERE Date# = '6/9/2004'

Pls help me,
Thanks a million

try it with square brackets around Date#

it's not a good idea to use special characters in a column name

I tried using Date and [Date] as well
both does not work.

Anyway is it something to do with the datatype?
And how do i filter? Do i filter by keying '6/9/2004' or '6/9/2004 12:24:35 PM'?

do you know how to generate a script for this table in Enterprise Manager?

i'd like to see the CREATE TABLE statement and a few sample rows

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SHI]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[SHI]
GO

CREATE TABLE [dbo].[SHI] (
[Value1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Value2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Date#] [datetime] NULL
) ON [PRIMARY]
GO

May be date format is different. have u tried with

SELECT * from mytable WHERE Date# = '9/6/2004 12:24:35 PM'

I finally got the format needed using MS SQL Server Query Analyser.
I actually need to key in '2004-06-09 12:24:35.007'

But is there a way to only filter by the date only? That mean i just wanted to key in the date not the time in order to filter.

Actually I need a statement in order to retrieve the record where only the record for past 7 days from the current date at the of point is retrieve.

So right now i got 2 problem:
First, not able to filter by just keying date.
Second, need to statement to capture the current date and minus 7 days and retrieve the past 7 days record.

Pls help me as i have read up a few books but still no answer.
Thanks a lot

1. Change the date according to ur key in date. append 23:59:59, which is the last second of that date. This will retrieve the records with the keyed in date no matter what time it has got. I think its better to format the date dd-mmm-yy to avoid the month and day problem.

Eg:

SELECT *
FROM SHI
WHERE (Date# BETWEEN '10-feb-02' AND '10-feb-02 23:59:59')


2. To get the last 7 days records

SELECT *
FROM SHI
WHERE (Date# BETWEEN DATEADD([day], - 7, GETDATE()) AND GETDATE())

Thanks to all experts, problem is solved.

Hi experts, i am problem regarding date filtering again. Pls help me.

select value1
from table1
where (date# between dateadd([day], -7, getdate()) and getdate())

The statement is correct but it takes the time of getdate() into consideration.
If i execute this statement at 26/6/04 1500hrs, the records show the record from 19/4 - 26/4 but records before 19/4 1500hrs it will not show. But i need the whole records on 19/4.

So how can i ignore the time while filtering?

where date#
between cast(convert(char(10),dateadd([day], -7, getdate()),120) as date)
and getdate()

do the same to the upper end if necessary

I got an error:
Type date is not a defined system type

where date#
between cast(convert(char(10),dateadd([day],-7, getdate()),120) as date) and getdate()

I have check try changing the name date after the AS to other name and i will also say : Type othername is not a defined system type

oops, my bad

where date#
between cast(convert(char(10),dateadd([day],-7, getdate()),120) as datetime) and getdate()

Thanks a lot, i just figure out also.
Really thanks a lot