0

In my SQL query in Access 2000, I have a field containing numbers like:

  • 0,15
  • 1,30
  • 1
  • 0,50

Where x, is the hour, and ,xx is the minutes.

I need to convert this field into a DateTime (or the Access equivalent) field, because I later need to sum on the time values in a report.

How can I convert that field into DateTime?

Brock Adams
  • 82,642
  • 19
  • 207
  • 268
Tommy Jakobsen
  • 2,213
  • 4
  • 37
  • 59

3 Answers3

0

Assuming your column with numeric data is named "Num_Time" you can use this query to get a Date column with that time info:

select CDate(CStr(Fix(Num_Time)) + ':' + 
   CStr(CLng((sgn(Num_Time) * Num_Time - Fix(Num_Time)) * 100)) + ':00') as RealTime
from myTable

The query converts your column in a string of format "hh:mm:ss" which can be passed to the CDate function to get a Date object with the given time. (The query doesn't take care of null or invalid values in the column.)

MicSim
  • 25,056
  • 15
  • 84
  • 124
0

In order to construct a DATETIME value you need a date (!!)

I'm not sure your exact usage but to aggregate the values you could consider using an INTEGER value of a certain time granule e.g. minutes:

• 0,15  ->  15
• 1,30  ->  90
• 1     ->  60
• 0,50  ->  50
onedaywhen
  • 50,748
  • 12
  • 91
  • 132
0

If you time goes over a day, not sure what having a datetime is going to do for you. You need to get the total minutes and convert to hours : minutes.

Select
    CInt(Left([Num_Time],InStr([Num_Time],",")))*60 + CInt(Right([Num_Time],Len([Num_Time])-InStr([Num_Time],","))) as [Total_Minutes]
    From [Your_Table]

This turns your hours to minutes plus the other minutes. Now you just have to turn the sum of all the minutes back to hours.

JeffO
  • 7,649
  • 3
  • 39
  • 52