Returning Today's date

[Yeltum]

Hi,

I using the following access sql:
s-sql="SELECT * FROM Events WHERE (SortDate=Date());"
to return events happening today on a ASP - which works
fine apart from one small problem - the server where my
database is held is in a different time zone (- 8 hours).
How can I adjust this statement to make it my time zone
(GMT).

I have tried the following, which does not work:
s-sql="SELECT * FROM Events WHERE (SortDate=Date()+0.33);"

Any help much appeciated.

 

[Steve Schapel]

Yeltum,

Try this...
WHERE SortDate=DateValue(DateAdd("h",8,Now()))

- Steve Schapel, Microsoft Access MVP

 

[Yeltum]

Thanks Steve,

I just had to change it to:
s-sql="SELECT * FROM Events WHERE SortDate=DateValue
(DateAdd('h',8,Now()));"

which is returing todays events, however at moment both
time zones are in same day - I'll test it later
tonight !!
Thanks for your help.

-----Original Message-----
Yeltum,

Try this...
WHERE SortDate=DateValue(DateAdd("h",8,Now()))

- Steve Schapel, Microsoft Access MVP

 

[Tom Ellison]

Dear Yeltum:

Does the SortDate have a time recorded in it, or just date? You
cannot adjust a date for time zones without knowing the time of day.

Next, it doesn't matter where the server resides. What matters is the
time zone reference of the people making the postings. The exception
to this would be if the server were date/time stamping the records as
they are added. However, if you are using Jet, it wouldn't be the
server that is time stamping the records, but the individual
workstations.

I have to think the workstations may be distributed across time zones,
since yours appears to be one of them. Each one would have its own
time setting. So it may be that your data is recorded in many
different time zones.

Unless you have examined and concluded how to handle all these
factors, trying to craft a solution may be more difficult than you
think!

You would have to adjust each entry to GMT (or some time zone selected
as your "standard") when it is entered, or you would have to record a
way of knowing in which time zone it was entered. Otherwise, such
disctinctions are going to be impossible.

Hi,

I using the following access sql:
s-sql="SELECT * FROM Events WHERE (SortDate=Date());"
to return events happening today on a ASP - which works
fine apart from one small problem - the server where my
database is held is in a different time zone (- 8 hours).
How can I adjust this statement to make it my time zone
(GMT).

I have tried the following, which does not work:
s-sql="SELECT * FROM Events WHERE (SortDate=Date()+0.33);"

Any help much appeciated.

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts