Dear All,
I have a query as below.It works fine . The problem is I have time range for me to select the data normaly on daily basis. My problem here is the dateTimeStartJourney and dateTimeEndJourney is stored as GMT 0. So when I search I would like to define that the time is by adding a particular +/-GMT. I have done on Date_Add accordingly but how to refine my search so that the between also is taking the +/-GMT? Thank you.
SELECT tblLocationFrom.geoFenceName As locationFrom, tblLocationTo.geoFenceName As locationTo,
tblAssociate.associateID,CAST(Date_Add(tblAssociate.dateTimeStartJourney, Interval '+08:00' hour_minute) AS CHAR) As dateTimeStartJourney,
CAST(Date_Add(tblAssociate.dateTimeEndJourney, Interval '+08:00' hour_minute) AS CHAR) As dateTimeEndJourney,
tblAssociate.totalC,tblAssociate.totalD,tblAssociate.totalE,tblAssociate.totalP,tblAssociate.totalS,tblAssociate.totalW
FROM tblAssociate JOIN tblGeoFence AS tblLocationFrom ON tblAssociate.locationFromID = tblLocationFrom.geoFenceID
JOIN tblGeoFence AS tblLocationTo ON tblAssociate.locationToID = tblLocationTo.geoFenceID
WHERE tblAssociate.dateTimeStartJourney between '2011-10-19 00:00:00'
And '2011-10-20 23:59:59'