How can I find records based upon a date and time range while accounting for time zone difference

[BJC]

Can someone help me? I need to find all records in a particular table
that occured within the last seven days; but also need to adjust for a
one hour time difference in time zone. The transactions occur in
Central time but are stored on our server at local time (Eastern).

This is the current query we're running to find all the transaction
within the last 7 days, but it does not figure in a time difference of
1 hour for the time zones..... It is automated so we need to use
variables. I need all the records for the last seven days that
occured between 1AM on the first day and 1AM on the last day (example:
between 1 AM on 3/2/07 and 1 AM on 3/9/07).

SELECT dbo_Trans.SiteID, dbo_Sites.SiteDesc, dbo_Trans.ServDate,
dbo_Trans.ID, dbo_Trans.PayType, dbo_Trans.Paid,
CInt(dbo_Trans.UserID)<0 AS UserID
FROM dbo_Trans INNER JOIN dbo_Sites ON dbo_Trans.SiteID =
dbo_Sites.SiteID
WHERE (((dbo_Trans.ServDate)>=Date()-8 And
(dbo_Trans.ServDate)<Date()-1) AND ((dbo_Trans.PayType)=51))
ORDER BY dbo_Trans.ServDate;

I'm sure there's a much better way to accomplish this. Please help.
Thank you!

 

[pietlinden]

Can someone help me? I need to find all records in a particular table
that occured within the last seven days; but also need to adjust for a
one hour time difference in time zone. The transactions occur in
Central time but are stored on our server at local time (Eastern).

This is the current query we're running to find all the transaction
within the last 7 days, but it does not figure in a time difference of
1 hour for the time zones..... It is automated so we need to use
variables. I need all the records for the last seven days that
occured between 1AM on the first day and 1AM on the last day (example:
between 1 AM on 3/2/07 and 1 AM on 3/9/07).

SELECT dbo_Trans.SiteID, dbo_Sites.SiteDesc, dbo_Trans.ServDate,
dbo_Trans.ID, dbo_Trans.PayType, dbo_Trans.Paid,
CInt(dbo_Trans.UserID)<0 AS UserID
FROM dbo_Trans INNER JOIN dbo_Sites ON dbo_Trans.SiteID =
dbo_Sites.SiteID
WHERE (((dbo_Trans.ServDate)>=Date()-8 And
(dbo_Trans.ServDate)<Date()-1) AND ((dbo_Trans.PayType)=51))
ORDER BY dbo_Trans.ServDate;

I'm sure there's a much better way to accomplish this. Please help.
Thank you!

did you look here yet?
http://vbnet.mvps.org/index.html?code/locale/gettimezonebias.htm
Yes, it's a VB site, not an Access site, but if you create a function,
you can call it in your query. Then you should be good to go.

 

[Michael Gramelspacher]

Can someone help me? I need to find all records in a particular table
that occured within the last seven days; but also need to adjust for a
one hour time difference in time zone. The transactions occur in
Central time but are stored on our server at local time (Eastern).

This is the current query we're running to find all the transaction
within the last 7 days, but it does not figure in a time difference of
1 hour for the time zones..... It is automated so we need to use
variables. I need all the records for the last seven days that
occured between 1AM on the first day and 1AM on the last day (example:
between 1 AM on 3/2/07 and 1 AM on 3/9/07).

SELECT dbo_Trans.SiteID, dbo_Sites.SiteDesc, dbo_Trans.ServDate,
dbo_Trans.ID, dbo_Trans.PayType, dbo_Trans.Paid,
CInt(dbo_Trans.UserID)<0 AS UserID
FROM dbo_Trans INNER JOIN dbo_Sites ON dbo_Trans.SiteID =
dbo_Sites.SiteID
WHERE (((dbo_Trans.ServDate)>=Date()-8 And
(dbo_Trans.ServDate)<Date()-1) AND ((dbo_Trans.PayType)=51))
ORDER BY dbo_Trans.ServDate;

I'm sure there's a much better way to accomplish this. Please help.
Thank you!

Maybe:

WHERE dbo_Trans.ServDate BETWEEN dateadd("h",1,Dateadd("d", datediff("d",0,now
())-8,0))AND dateadd("h",1,Dateadd("d", datediff("d",0,now())-1,0))