SQL Date/Time Question

  • Thread starter Thread starter Clive Minnican
  • Start date Start date
C

Clive Minnican

Hello,

I have a database table called Customers and I want to use SQL to display
all the records between 2 specific times for each day e.g. between 08:00:00
and 18:00:00.

The following query works:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #2004-01-01 08:00:00# AND #2004-03-31
18:00:00#
ORDER BY [Date];

but it doesn't restrict the times for each day to the times specified.

I have tried using this query instead:

SELECT * FROM Customers
WHERE [ID]=83
AND ([Date] BETWEEN #2004-01-01# AND #2004-03-31#)
AND ([Date] BETWEEN #08:00:00# AND #18:00:00#)
ORDER BY [Date];

but it returns no results! I have also tried:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #08:00:00# AND #18:00:00#
ORDER BY [Date];

but this returns no results either :(

Please tell me if there is a way to achieve this. I'm sure there must be a
simple solution.

I am connecting to an Access database via ODBC with Visual C++ 6.0 so I can
only use normal SQL and not custom Access functions.

Many thanks,

Clive.
 
Perhaps you can use

WHERE TimeValue([Date]) Between #08:00:00 AM# AND #18:00:00#
 
Try this:

SELECT * FROM Customers
WHERE [ID]=83
AND DateValue([Date]) BETWEEN #2004-01-01# AND #2004-03-31#
AND TimeValue([Date]) BETWEEN #08:00:00# AND #18:00:00#

ORDER BY [Date];
 
Thank you very much - it works perfectly!

Duane Hookom said:
Try this:

SELECT * FROM Customers
WHERE [ID]=83
AND DateValue([Date]) BETWEEN #2004-01-01# AND #2004-03-31#
AND TimeValue([Date]) BETWEEN #08:00:00# AND #18:00:00#

ORDER BY [Date];

--
Duane Hookom
MS Access MVP


Clive Minnican said:
Hello,

I have a database table called Customers and I want to use SQL to display
all the records between 2 specific times for each day e.g. between 08:00:00
and 18:00:00.

The following query works:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #2004-01-01 08:00:00# AND #2004-03-31
18:00:00#
ORDER BY [Date];

but it doesn't restrict the times for each day to the times specified.

I have tried using this query instead:

SELECT * FROM Customers
WHERE [ID]=83
AND ([Date] BETWEEN #2004-01-01# AND #2004-03-31#)
AND ([Date] BETWEEN #08:00:00# AND #18:00:00#)
ORDER BY [Date];

but it returns no results! I have also tried:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #08:00:00# AND #18:00:00#
ORDER BY [Date];

but this returns no results either :(

Please tell me if there is a way to achieve this. I'm sure there must be a
simple solution.

I am connecting to an Access database via ODBC with Visual C++ 6.0 so I can
only use normal SQL and not custom Access functions.

Many thanks,

Clive.
Click to expand...
Click to expand...
 
Duane
When I used the line alone:
TimeValue([TimeOn]) Between #09:30:00# AND #16:00:00#,
The query returns the desired records.

But, when I looked in my test query's SQL it had been changed to:
TimeValue([TimeOn]) Between #12/30/1899 9:30:0# AND #12/30/1899 16:0:0#.

Is this normal? I have a sub-query that pre-selects my date range.
I plan to use this method in my QueryDef (without the 12/30/1899)if its
reliable.
Thanks
Sid

Clive Minnican said:
Thank you very much - it works perfectly!

Duane Hookom said:
Try this:

SELECT * FROM Customers
WHERE [ID]=83
AND DateValue([Date]) BETWEEN #2004-01-01# AND #2004-03-31#
AND TimeValue([Date]) BETWEEN #08:00:00# AND #18:00:00#

ORDER BY [Date];

--
Duane Hookom
MS Access MVP


Clive Minnican said:
Hello,

I have a database table called Customers and I want to use SQL to display
all the records between 2 specific times for each day e.g. between 08:00:00
and 18:00:00.

The following query works:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #2004-01-01 08:00:00# AND #2004-03-31
18:00:00#
ORDER BY [Date];

but it doesn't restrict the times for each day to the times specified.

I have tried using this query instead:

SELECT * FROM Customers
WHERE [ID]=83
AND ([Date] BETWEEN #2004-01-01# AND #2004-03-31#)
AND ([Date] BETWEEN #08:00:00# AND #18:00:00#)
ORDER BY [Date];

but it returns no results! I have also tried:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #08:00:00# AND #18:00:00#
ORDER BY [Date];

but this returns no results either :(

Please tell me if there is a way to achieve this. I'm sure there must
Click to expand...
Click to expand...
be
a I
can
Click to expand...
Click to expand...
 
Access Date/Time fields are meant for timestamps (both Date and Time).
Values are stored as 8 byte floating point numbers, where the integer part
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal part represents the time as a fraction of a day. If all you're using
is a time, Access assumes the day value is 0, which is 30 Dec, 1899.

If you've got a timestamp, and all you want is the time portion of it, you
can use the TimeValue function to strip off the date (and there's also a
DateValue function that will strip off the time and only return the date)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Sid said:
Duane
When I used the line alone:
TimeValue([TimeOn]) Between #09:30:00# AND #16:00:00#,
The query returns the desired records.

But, when I looked in my test query's SQL it had been changed to:
TimeValue([TimeOn]) Between #12/30/1899 9:30:0# AND #12/30/1899 16:0:0#.

Is this normal? I have a sub-query that pre-selects my date range.
I plan to use this method in my QueryDef (without the 12/30/1899)if its
reliable.
Thanks
Sid

Clive Minnican said:
Thank you very much - it works perfectly!

Duane Hookom said:
Try this:

SELECT * FROM Customers
WHERE [ID]=83
AND DateValue([Date]) BETWEEN #2004-01-01# AND #2004-03-31#
AND TimeValue([Date]) BETWEEN #08:00:00# AND #18:00:00#

ORDER BY [Date];

--
Duane Hookom
MS Access MVP


Hello,

I have a database table called Customers and I want to use SQL to display
all the records between 2 specific times for each day e.g. between
08:00:00
and 18:00:00.

The following query works:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #2004-01-01 08:00:00# AND #2004-03-31
18:00:00#
ORDER BY [Date];

but it doesn't restrict the times for each day to the times specified.

I have tried using this query instead:

SELECT * FROM Customers
WHERE [ID]=83
AND ([Date] BETWEEN #2004-01-01# AND #2004-03-31#)
AND ([Date] BETWEEN #08:00:00# AND #18:00:00#)
ORDER BY [Date];

but it returns no results! I have also tried:

SELECT * FROM Customers
WHERE [ID]=83 AND [Date] BETWEEN #08:00:00# AND #18:00:00#
ORDER BY [Date];

but this returns no results either :(

Please tell me if there is a way to achieve this. I'm sure there
Click to expand...
Click to expand...
must
be
Click to expand...
so
I
Click to expand...
Click to expand...
 
Back
Top