Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 26-02-2009
Member
 
Join Date: Nov 2008
Posts: 85
SQL SELECT with DateTime

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?
Reply With Quote
  #2  
Old 26-02-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
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# ...
Reply With Quote
  #3  
Old 26-02-2009
Member
 
Join Date: May 2008
Posts: 2,008
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'
Reply With Quote
  #4  
Old 26-02-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
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
Reply With Quote
  #5  
Old 26-02-2009
Member
 
Join Date: May 2008
Posts: 2,293
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
Reply With Quote
  #6  
Old 26-02-2009
Member
 
Join Date: Oct 2005
Posts: 2,389
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'
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "SQL SELECT with DateTime"
Thread Thread Starter Forum Replies Last Post
Datetime Perl module not installing Wampanoag Software Development 6 19-06-2010 12:31 AM
How to convert string into DateTime Segvoia Software Development 7 05-03-2010 02:55 PM
Compare two DateTime objects teena_pansare Software Development 3 28-11-2009 05:38 PM
out-of-range datetime value MobilePhoneGuru Software Development 3 09-10-2009 10:05 AM
DateTime Format String Zool Software Development 3 17-07-2009 03:20 PM


All times are GMT +5.5. The time now is 04:54 PM.