SQL syntax for retrieving datetime record

[Ogi]

Hi,

I've got to try to get a record by it's date/time stamp.
The datetime field contains "27/12/2001 12:00:30" (without the quotes!)

What is the correct syntax of my sql statement to return it, for example:-

SELECT * FROM TABLE1 WHERE [Date Time] = '27/10/2001 12:00:30'

As the above fails to work!!!!

Thanks in advance for any/all help given.
Ogi.

 

[sdh]

the correct syntax (if using jet(access) anyway) would be

SELECT * FROM TABLE1 WHERE [Date Time] = #27/10/2001 12:00:30#

 

[Ogi]

Hi,

Tried that, it didn't work, exact sql line reads:-

Select * From [TimeSheet Records] Where [Record Identity] = '#27/12/2001 20:48:07#'

And get :-

Run time error '-2147217913 (80040e07)
Syntax error converting datetime from character string.

If i remove the ' characters, the error reads:-

Run-time error '--2147217900 (80040e14)
Line 1:Incorrect syntax near '20'.

Any ideas?
Thanks for any/all help given.
Ogi.

 

[ubub]

Hi,

Using Jet4 and a table I use for one of my programs I tried this syntax to retrieve records it was successful with no errors. This uses an old style DAO database

Set rs = db.OpenRecordset("Select * From [MatchTable] Where [DatePlayed] = #10/8/2000 12:01:40 AM#", dbOpenDynaset)

Hope this is of some help

 

[Ogi]

Hi,

Fixed this by altering the date to 2001/01/01.

Cheers for you help and assistance.
Ogi.

 

[percy]

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.

I'm trying to update a field in an Acess DB which atributes are: length : 14, text.
I'm geting the data from an identhical field in another Acess table in the same DB and I got the above message.

The code:

Set Recor = Server.CreateObject("ADODB.Recordset&quot

sQuery = "SELECT socion, cgccpf, em_dia FROM Clubecad WHERE em_dia = 'S' or em_dia = 'N'"

If Recor(&quot;cgccpf&quot <> &quot;&quot; or Recor(&quot;cgccpf&quot <> &quot;0&quot; Then

Conexao.Execute &quot;UPDATE Tbsac SET cgccpfsac = '&quot; & Recor(&quot;cgccpf&quot & &quot;' WHERE codsac = &quot; & Recor(&quot;socion&quot
Else
Conexao.Execute &quot;UPDATE Tbsac SET cgccpfsac = '&quot; & cpf1 & &quot;' WHERE codsac = &quot; & nsocion
End If