Re: a sql query that doesn't return any record (performing date comparison)

mr.nimz@xxxxxxxxx wrote:
here is my table structure,

Name: Table1

Name Type Size
~~~~ ~~~ ~~~
szUserID Text 50
szOrgID Text 50
szAccountID Text 50
dtLoginDateTime Date/Time 8
dtLogoutDateTime Date/Time 8
numUnitsUsed Long Integer 4
szSessionID Text 50


sample data

szUserID szOrgID szAccountID dtLoginDateTime dtLogoutDateTime numUnitsUsed szSessionID
sa1111 bioe0001 2006-06-16 14:58:25 2006-06-16
15:00:57 0 g3ew0q55qg1g0x3vayivkx45
sa1111 bioe0001 2006-06-16 15:01:12 2006-06-16
15:04:38 0 g3ew0q55qg1g0x3vayivkx45
sa1111 bioe0001 2006-06-16 15:04:43 2006-06-16
15:06:59 0 g3ew0q55qg1g0x3vayivkx45
sa1111 bioe0001 2006-06-16 15:40:20 2006-06-16
15:41:04 0 g3ew0q55qg1g0x3vayivkx45
sa1111 BGB_Acc001 2006-06-16 16:35:51 2006-06-16
16:35:58 0 n3kpoivtcmfr2xigzyutlazk
sa1111 BGB_Acc001 2006-06-16 17:21:47 2006-06-16
17:26:23 0 u1qk3syh1fwbbk55v41qnayo

Note: I STORE DATETIME IN ISO Format (yyyy-mm-dd hh:nn:ss)

(sorry for disOrganized format, i donno how to paste in bettter way...)

HERE COMES the QUERY.This FAILS to return any record !!!!
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT dtLoginDateTime, szUserID, szOrgID, szAccountID
FROM Table1
WHERE dtLoginDateTime=#2006-06-16 14:58:25#

i got results when i tried it this way,.
~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT dtLoginDateTime, szUserID, szOrgID, szAccountID
FROM Table1
WHERE cdate(format(dtLoginDateTime,'General Date'))=#2006-06-16
14:58:25#

So what's Wrong and where,

Congratulations on using ISO format. Would that everyone did.

This works for me:

Sub temp()
Dim r As DAO.Recordset
DBEngine(0)(0).Execute "INSERT INTO Table1 ([text],testdate) VALUES
('d',#2006-06-16 14:58:25#)"
Set r = DBEngine(0)(0).OpenRecordset("SELECT * FROM Table1 WHERE
testdate=#2006-06-16 14:58:25#")
With r
.MoveLast
Debug.Print .RecordCount
End With
Set r = Nothing
End Sub

Sub test()
Dim z As Long
For z = 0 To 10
temp
Next z
End Sub

displaying in the Immediate Window

1
2
3
4
5
6
7
8
9
10
11

As you can see, WHERE testdate=#2006-06-16 14:58:25# seems to "work".

Is this a fair test of what you are saying does not work?
Is it substantively different from what you are doing?
Considering that JET stores datetime data as 8 bytes that are roughly
analagous to a double, what do you mean when you say, "I STORE DATETIME
IN ISO Format (yyyy-mm-dd hh:nn:ss)?"

My Windows local settings for date time are set to the ISO format. I
doubt if this makes a difference to JET's interpretation of #2006-06-16
14:58:25# but perhaps we should consider it.

.

Relevant Pages

  • Re: a sql query that doesnt return any record (performing date comparison)
    ... Name: Table1 ... Congratulations on using ISO format. ... Considering that JET stores datetime data as 8 bytes that are roughly ...
    (comp.databases.ms-access)
  • Re: Splitting and comparing file names
    ... with the general format would really be much preferred... ... sample data would be nice. ... You have a case of premature declaration. ...
    (perl.beginners)
  • Re: hash key not found .. WHY NOT?
    ... filea.csv format: ... Where file1.csv is using BK=AK to lookup A3 and A4. ... Not a word of this makes sense nor matches your sample data. ... Please show REAL sample input and REAL sample output. ...
    (perl.beginners)
  • ADO Recordset Question
    ... I need help in ADO syntax for a particular function. ... I have a table (table1) ... which is setup in a flat table format. ... There only should be rows in table2 when an employee ...
    (microsoft.public.access.modulesdaovba)
  • RE: Nobody can answer! Problem with commas and dots
    ... cases of your sample data. ... running my code on string format numbers still works. ... The Replacefunction can change commas to dots, ...
    (microsoft.public.excel.programming)