Re: SQL SELECT with DateTime
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# ...
Re: SQL SELECT with DateTime
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'
Re: SQL SELECT with DateTime
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
Re: SQL SELECT with DateTime
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
Re: SQL SELECT with DateTime
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'