Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • Last Modified:

Query records of current date minus 6 hours

Microsoft access. From a link table I need a query that selects all the fields for a table called DATA, however this has to select records from the current date minus 6 hours, but the field DATE is setup as date
anyway to this this
0
teogos
Asked:
teogos
  • 8
  • 6
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
does the field DATE have hours on it?

select *
from [Data]
where [Date] <=Dateadd("h",-6,Now())
0
 
teogosAuthor Commented:
No hours just date
0
 
Rey Obrero (Capricorn1)Commented:
then, you can not get anything based on your criteria.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Rey Obrero (Capricorn1)Commented:
you may have to change the default format to  General Date
0
 
teogosAuthor Commented:
This Link Table comes from a SQL table setup as Datetime
0
 
teogosAuthor Commented:
Well, since this cannot be done on Access, now let ask you this  with the following code what I want to accomplish works fine, but how can get this into a SQL query, I run this on the query analizer and work fine, but how do I convert to a procedure

DECLARE @startdate datetime
IF datepart(hour, getdate()) < 6
     SET              @startdate = CAST(CONVERT(CHAR(10), getDate() - 1, 121) AS DateTime)
ELSE
     SET              @startdate = CAST(CONVERT(CHAR(10), getDate(), 121)  AS DateTime)

select *, @startdate
from DATA
WHERE YEAR(Date) = YEAR(@startdate) and month(date) = month(@startdate) and day(date) = day(@startdate)
0
 
Christopher KileCommented:
You're confusing me.  Your test query checks to see if the current time is less than 6 AM.  Why aren't you using DateDiff()? Versions exist in both Access and SQL Server.

In the following examples, I'm going to call your date field theDate to avoid keyword collision.

Do you want to match a date which is at least six hours previous to a given date?

SELECT * FROM DATA
WHERE DateDiff("h", theDate,  Now()) <= 6

0
 
teogosAuthor Commented:
Where I want is to show records after midnight that from the same date example today is 4/5/2007
however after midnight when I run the query, Even so the system date is 4/6/2007 I want to show records from the 4/5/2007. Then after 6:00 AM on the 4/6/2007 I am back to normal date or current
0
 
Christopher KileCommented:
In Access, this would be:

SELECT * FROM DATA
WHERE
Format("yyyymmdd", theDate) =
Format("yyyymmdd", iif(datepart("h", Now()) < 6, DateAdd("d", -1, Date), Date()))

Since your original request was for an Access solution, perhaps this will do?
0
 
teogosAuthor Commented:
no does not work it brings all the records from previous days, I just want to see today's
0
 
Rey Obrero (Capricorn1)Commented:
teogos,
if you don't have a time value from the fieldDate, you will not get the correct records.
0
 
Christopher KileCommented:
Run the following query against your database:

SELECT
theDate 'Substitute your field name here
,
CDate(Format("yyyymmdd", theDate))
,
CDate(Format("yyyymmdd", iif(datepart("h", Now()) < 6, DateAdd("d", -1, Date), Date())))
FROM
Data
Order by theDate DESC

You should be able to see immediately why the dates don't match.  If you have trouble fixing it or understanding what you're seeing, please send me some samples.  Thanks in advance.
0
 
teogosAuthor Commented:
Date column shows dates in desc order  
column 2   shows #Error
column 2  shows #Error
0
 
teogosAuthor Commented:
Anything you guys can help me. If I have to a query on the SQL or VBA for access, I am increasing the points , the only time I am looking for is after midnight to see the records for today's
0
 
Rey Obrero (Capricorn1)Commented:

try this

select *
from Data
where ((Data.FieldDate)=iif(timevalue(now)<= timeSerial(6,0,0), Date()-1,Date()));

0
 
Rey Obrero (Capricorn1)Commented:
or

select *
from Data
where ((Data.FieldDate)=iif(timevalue(now)>= timeSerial(0,0,0) And timevalue(now)<= timeSerial(6,0,0), Date()-1,Date()));
0
 
teogosAuthor Commented:
Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 8
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now