Matching on Dates & times

  • Thread starter Thread starter Bunky
  • Start date Start date
What John is suggesting is using a 24 hour instead of 12 hour with AM/PM.

Another test along John's line of thinking is to actually check the
numerical value of the time.
SELECT [Call Data].[Timestamp], Val([Call Data].[Timestamp]) AS Time_Number
FROM [Call Data]
ORDER BY [Call Data].[Timestamp];

Another check is on the data of forecast table --
SELECT CTActiveForecast100809.[#fields:date],
Right([#fields:date],4) AS CkYear, Left([#fields:date],2) AS CkMonth,
Right(Left([#fields:date],4),2) AS CkDay
FROM CTActiveForecast100809;

You made an error in changing my fields for yours. Try this --
SELECT CTActiveForecast100809.[#fields:date],
DateSerial(Right([#fields:date],4),Left([#fields:date],2),Right(Left([#fields:date],4),2))+CVDate([Period]) AS Forecast_Date
FROM CTActiveForecast100809;
 
Karl / John,

Thank you so very much in assisting me to get these two files joined
correctly. I had started using Karl's method but will keep John's in
abayence. You guys are absolutely the best of the best! Thank you again!

Kent

KARL DEWEY said:
What John is suggesting is using a 24 hour instead of 12 hour with AM/PM.

Another test along John's line of thinking is to actually check the
numerical value of the time.
SELECT [Call Data].[Timestamp], Val([Call Data].[Timestamp]) AS Time_Number
FROM [Call Data]
ORDER BY [Call Data].[Timestamp];

Another check is on the data of forecast table --
SELECT CTActiveForecast100809.[#fields:date],
Right([#fields:date],4) AS CkYear, Left([#fields:date],2) AS CkMonth,
Right(Left([#fields:date],4),2) AS CkDay
FROM CTActiveForecast100809;

You made an error in changing my fields for yours. Try this --
SELECT CTActiveForecast100809.[#fields:date],
DateSerial(Right([#fields:date],4),Left([#fields:date],2),Right(Left([#fields:date],4),2))+CVDate([Period]) AS Forecast_Date
FROM CTActiveForecast100809;

--
Build a little, test a little.


Bunky said:
Hi John!
I tried to do this but I was never able to get the time to show AM/PM. And
we need to have that since our calls start at 8 AM and stop at 9 PM. The
reporting is one every 15 min intervals so there are 52 intervals per day.
Any ideas how to format each to a date and time and still keep the interval?
Click to expand...
Click to expand...
 
Back
Top