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

[mr.nimz]

hello,

this is antenio. recently i've come to a problem. i got a way through
it, somehow, still it left me in a curious state, so i'm posting it
here, if i can get an answer from some techy,

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,

thanks in advance.
Best regards,
Antenio.

[Lyle Fairfield]

mr*****@gmail.com 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.

[Allen Browne]

Internally, Access stores dates as floating point numbers, where the integer
part represents the date, and the fraction represents the time of day (.5 =
noon; .25 = 6am (one quarter of a day), and so on.) That means date/time
values are subject to the same rounding issues that plague floating point
values.

The reason your workaround succeeds is that it forces the date value to be
the value to the nearest second that you are trying to match.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<mr*****@gmail.com> wrote in message
news:11**********************@b68g2000cwa.googlegr oups.com...

this is antenio. recently i've come to a problem. i got a way through
it, somehow, still it left me in a curious state, so i'm posting it
here, if i can get an answer from some techy,

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,

[mr.nimz]

Lyle Fairfield wrote:

mr*****@gmail.com 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.

i forget to mension that,
My windows (XP SP2) Regional settings are English (US).
In database i've set format of datetime field as "yyyy-mm-dd hh:nn:ss",
And Whenever i store dates, i convert them in ISO Format. viceversa.
I'm Using ADO.NET (Oledb Provider)

thanks for your time,

best regards,
Antenio.

[Lyle Fairfield]

Allen Browne wrote:

Internally, Access stores dates as floating point numbers, where the integer
part represents the date, and the fraction represents the time of day (.5 =
noon; .25 = 6am (one quarter of a day), and so on.) That means date/time
values are subject to the same rounding issues that plague floating point
values.
Allen Browne wrote: Internally, Access stores dates as floating point numbers, where the integer
part represents the date, and the fraction represents the time of day (.5 =
noon; .25 = 6am (one quarter of a day), and so on.) That means date/time
values are subject to the same rounding issues that plague floating point
values.

The rounding issues that "plague!" [in my opinion this is like saying
that there are only two halves to every orange is a BIG problem]
floating point values occur when we perform arithmetic operations on
them, or when we try to compare them with some literal (string). Unless
we are so foolish as to try to perform arithmetic on date-times these
issues should never cause problems. The format in which dates are
stored can differentiate to a degree one and half million times greater
than that required to differentiate between seconds. Surely, the rules
for storage are simply the rules of recovery reversed! I very much
doubt that any rounding issue exists in the storage, retrieval or
comparison of dates which have been dealt with properly as dates and
not suffered from coercion into doubles. The OP's post gives no
indication that his/her dates have been handled carelessly.

The OP now indicates he is using ADO.Net. I guess I am not sufficiently
versed in ADO.Net to have considered using VBA functions within SQL (as
he does in his second SQL string) used in it, and I think I will keep
that position for a long time.

In any case, I believe that within Access/JET/DAO/ADO there is no
difficulty in recovering a record based on its meeting the criteria of
a date-time field being equal to a date-time expressed as #YYYY-MM-DD
hh:nn:ss#.

For anyone who is bored and wants to spend a little time reflecting on
date-times in VBA I offer:

Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(Destination As Any, _
Source As Any, _
ByVal Length As Long)

Public Sub ExploreDateStorage()
Dim b(8) As Byte
Dim d As Date
Dim z As Long

d = DateSerial(2006, 6, 16) + TimeSerial(14, 58, 25)

For z = 0 To 9
CopyMemory b(0), d, 8
Debug.Print _
Format(b(0), "000\."); _
Format(b(1), "000\."); _
Format(b(2), "000\."); _
Format(b(3), "000\."); _
Format(b(4), "000\."); _
Format(b(5), "000\."); _
Format(b(6), "000\."); _
Format(b(7), "000\ "); _
StrConv(b, vbUnicode)
d = DateAdd("s", 1, d)
Next z

d = DateSerial(2006, 6, 16) + TimeSerial(14, 58, 25)

For z = 0 To 9
CopyMemory b(0), d, 8
Debug.Print _
Format(b(0), "000\."); _
Format(b(1), "000\."); _
Format(b(2), "000\."); _
Format(b(3), "000\."); _
Format(b(4), "000\."); _
Format(b(5), "000\."); _
Format(b(6), "000\."); _
Format(b(7), "000\ "); _
StrConv(b, vbUnicode)
d = d + 1 / (2 ^ 37)
Next z

Debug.Print CDec(1 / (2 ^ 37))

Debug.Print (CDec(1 / 86400)) / (CDec(1 / (2 ^ 37)))

End Sub

[Lyle Fairfield]

[e-mail] wrote:

I'm Using ADO.NET (Oledb Provider)

Perhaps, we could see the exact and complete line of code, or property
assignment where the SQL statement is used.

[Nimz]

Lyle Fairfield wrote:

[e-mail] wrote:

I'm Using ADO.NET (Oledb Provider)

Perhaps, we could see the exact and complete line of code, or property
assignment where the SQL statement is used.

hello,
here is the code that uses the query.

Private Sub BindData()

Dim cn As New OleDbConnection

Try
cn.ConnectionString = sConnectionString

Dim da As New OleDbDataAdapter(sSQLQuery, cn)
Dim ds As New DataSet

da.Fill(ds)

dbGrid.DataSource = ds

dbGrid.DataBind()

lblCount.Text = "Details found" +": " +
ds.Tables(0).Rows.Count.ToString
Catch ex As Exception
lblError.Text += "Error description" + ": " +
ex.Message() + "<p>"
Finally
cn.Close()
End Try

end sub

AND, here is extra stuff,

1) "ConnectionString"="Provider=Microsoft.Jet.OLEDB.4 .0;Data
Source="db\usrmgtVer1.mdb";User Id=admin;Password="/>

(taken from web.config file of my ASP.net Site, altered it from
original string, removed some text)

2) sSQLQuery = same sql that i mentioned in my post.

[Lyle Fairfield]

Nimz wrote:

Lyle Fairfield wrote:

[e-mail] wrote:

I'm Using ADO.NET (Oledb Provider)

Perhaps, we could see the exact and complete line of code, or property
assignment where the SQL statement is used.

hello,
here is the code that uses the query.

Private Sub BindData()

Dim cn As New OleDbConnection

Try
cn.ConnectionString = sConnectionString

Dim da As New OleDbDataAdapter(sSQLQuery, cn)
Dim ds As New DataSet

da.Fill(ds)

dbGrid.DataSource = ds

dbGrid.DataBind()

lblCount.Text = "Details found" +": " +
ds.Tables(0).Rows.Count.ToString
Catch ex As Exception
lblError.Text += "Error description" + ": " +
ex.Message() + "<p>"
Finally
cn.Close()
End Try

end sub

AND, here is extra stuff,

1) "ConnectionString"="Provider=Microsoft.Jet.OLEDB.4 .0;Data
Source="db\usrmgtVer1.mdb";User Id=admin;Password="/>

(taken from web.config file of my ASP.net Site, altered it from
original string, removed some text)

2) sSQLQuery = same sql that i mentioned in my post.

I can't duplicate this problem. From what you have said, the
OleDbDataAdapter does not seem to have a problem with #YYYY-MM-DD
hh:nn:ss#. It seems to have a problem with your date-time field. I
cannot think of any reason that this should be so.
I suppose this situation underlines the advisability, in this case and
most or all others, of declaring properly typed variables, in this case
Parameters, and using them. VBA is an evil language in that it actively
abets non-typed coding; I suspect that God has a special place in Hell
for its creators, but the type of punishment will constantly shift from
fire to starvation to thirst to beatings to listening to religious
fundamentalists with neither rhyme nor reason.