How VBA interprets datetime format

  • Thread starter Thread starter Scott
  • Start date Start date
S

Scott

I have a datetime field and i'm trying to return records from 5/24/2004
5:30:00 AM to 5/25/2004 5:29:59 AM.

If I use a view, the correct records are returned. If I use VBA and open an
ADO recordset illustrated below, I get very strange numbers that are
definetly not correct. What time of datetime syntax does VBA need to
properly filter a recordset? Both FIGURE 1 and FIGURE 2 return incorrect
numbers. I can't tell what VBA is converting my datetime filter to. Any
help?


FIGURE 1

rs.Open "SELECT ShiftName, Sum(Downtime) AS SumOfDowntime " & _
"FROM mytable " & _
"WHERE myDateTimeField Between #5/24/2004 5:30:0# " & _
"AND #5/25/2004 5:29:59# " & _
"GROUP BY ShiftName"



FIGURE 2

rs.Open "SELECT ShiftName, Sum(Downtime) AS SumOfDowntime " & _
"FROM mytable " & _
"WHERE myDateTimeField Between #5/24/2004 5:30:00 AM# " & _
"AND #5/25/2004 5:29:59 AM# " & _
"GROUP BY ShiftName"
 
Both work from query window, but when vba runs them, vba is converting the
datetime wrong. There's some type of datetime format issue with vba.

i'd love to find out the trick, but i'm now using a sproc and letting sql do
the datetime convertion.

I do have one more issue, but i've already posted it. I'm going to re-post
it right here again, just because i've been fighting it for 2 days. PLEASE
don't anyone reem me out for double-posting, i'm just desperate for time!

My only remaining problem is my SPROC that I want to pass a
single date variable like 5/24/2004 and return records from 5/24/2004 5:30
AM to 5/25/2004 5:29.59 AM.

FIGURE 1 runs find with datetime hardcoded in the WHERE clause. I need help
building my where clause in FIGURE 2 so the SPROC concatenates the 5:30 AM
and 5:29:59 AM to the variable dtStartDate and filters like FIGURE 1. Any
ideas?


FIGURE 1

CREATE PROCEDURE GetDowntime
(
@dtStartDate datetime
)
AS
BEGIN
SET NOCOUNT ON

SELECT ShiftID AS Shift, downtime AS Downtime
FROM mytable
WHERE (myDateTimefield >= CONVERT(DATETIME, '2004-05-24
05:30:00', 102)) AND
(myDateTimefield <= CONVERT(DATETIME, '2004-05-25
05:29:59', 102))
END
GO



FIGURE 2

WHERE CONVERT (varchar(12), myDateTimefield, 101) >= @dtStartDate
 
Back
Top