SQL SELECT with DateTime

[Coldman]

hi there

I have a MS Access database table with a DateTime field in it. The following INSERT statement works:

INSERT INTO Table VALUES(...., '4/26/2007 5:25:59 PM', ....)

but the SELECT statement never works when I specified the same string (it gives me Data Type Mismatch error):

SELECT * FROM Table WHERE--- AND Time = '4/26/2007 5:25:59 PM' ...

What is the correct way to do this?

[Praetor]

Time" is a reserved word AND a function in access and shouldn't be used by itself as a field name, if possible, change it to TheTime or MyTime. Otherwise you need to surround it with brackets as shown.

The reason your insert statement works is because you're inserting the date as a text string, but happens to fit the General Date/Time format.

The select doesn't work the same way, it's value based and therefor the criteria has to match the data type for the field. Surrounding the date/time with # # tells Access Jet that it's a date.

SELECT * FROM Table WHERE .... AND [Time] = #4/26/2007 5:25:59 PM# ...

[Katty]

Create Table #Temp(DateTimeColumn DateTime)

Insert Into #Temp
Select '3/23/2006' Union All
Select '3/24/2006' Union All
Select '3/24/2006 3:33:25 PM'

-- This returns 2 records because of the >
Select *
From #Temp
Where DateTimeColumn > '3/23/2006'

-- This returns 3 records because of the >=
Select *
From #Temp
Where DateTimeColumn >= '3/23/2006'

[kelfro]

select slot_item.* from item_master, slot_item WITH (NOLOCK)
WHERE (slot_item.sku_id=item_master.sku_id
AND slot_item.sku_id <> 0 AND item_master.whse_code = 'SE')
AND ( ((slot_item.slot_id is null)
-- and (item_master.ex_recpt_date>={d '2006-03-23'})
and (item_master.ex_recpt_date<={d '2006-04-28'})
and ( slot_item.sku_id
IN ( SELECT sku_id FROM item_cat_xref WITH (NOLOCK)
WHERE cat_code_id=8) ) ) )
ORDER BY item_master.sku_name ASC

[Zecho]

create table tt (d datetime)
insert into tt
select '3/23/2006 9:18:57 PM' union all
select '3/24/2006 0:00:00 AM'

select * from tt

[Reegan]

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE = '2003-04-09

SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between '2003-04-09'
AND '2009-02-10'
SELECT * FROM DATE_SAMPLE
WHERE SAMPLE_DATE >= '2009-02-09'
AND SAMPLE_DATE <'2009-02-10'