Round Down Time to nearest 15min

[firefly2k8]

Is there an SQL function which will round down a time like 11:56:18 to 11:45 for example?

 

[vbaInet]

No such function. You can write your own.

 

[firefly2k8]

Ok I could do this perhaps using the Datepart function to breakout the minutes, hours etc. In Excel i would glue this back together with the time function. But it seems there is no equivalent in VBA?

 

[vbaInet]

At the moment I can only think of using a SELECT case statement. I will need to give it a bit more thought.

 

[firefly2k8]

Ok I think I have solved it with following functions. But how do I call this function in an SQL statement. is it just:

UPDATE MainTable
SET MainTable.RoundedTime = Module2.RoundedDate(MainTable.TimeStamp)

Function RoundDwn(ByVal InNum As Integer) As Integer
  
  If InNum < 15 Then
    RoundDwn = 0
   Else
      If InNum >= 15 And InNum < 30 Then
        RoundDwn = 15
      Else
        If InNum >= 30 And InNum < 45 Then
          RoundDwn = 30
        Else
          RoundDwn = 45
        End If
      End If
  End If

End Function



Function RoundedDate(ByVal InDate As Date) As Date

  RoundedDate = DateAdd("n", -DatePart("n", InDate), InDate)
  NewMins = RoundDwn(DatePart("n", InDate))
  RoundedDate = DateAdd("n", NewMins, RoundedDate)
  
End Function

 

[vbaInet]

You shouldn't be saving derived values. Why not just calculate it on the fly? Or is this going to be a one-off operation?

 

[vbaInet]

And here's your RoundDwn function using SELECT CASE:

Function RoundDwn(ByVal InNum As Integer) As Integer
  
    Select Case InNum
        Case Is < 15
            RoundDwn = 0
        Case 15 To 29
            RoundDwn = 15
        Case 30 To 44
            RoundDwn = 30
        Case Else
            RoundDwn = 45
    End Select
    
End Function

 

[firefly2k8]

Thanks for your reply, and the select case.

On the fly is probably best and I may move to that. For now though I am not sure how to apply a user defined function in an SQL statement. Doing...

      strSQL = "UPDATE [SE2 Project Table] " & _
                "SET [SE2 Project Table].[RoundedTime] = Module2.RoundedDate([SE2 Project Table].[Time Stamp]) " & _
                "WHERE  [SE2 Project Table].[Project ID] =" & Module1.ProjectID & ";"
      DoCmd.RunSQL strSQL

gives the error "Run-Time error 3085 Undefined function in expression".

 

[stopher]

Here's an expression that will round your time for you (no need for custom functions):

TimeSerial(Hour([mytime]),(Minute([mytime])\15)*15,0)

Regards
Chris

 

[vbaInet]

Well, there you have it from Chris... a much simpler way of doing it.

TimeSerial() can be used directly in your query.

 

[firefly2k8]

There is nothing like re-inventing the wheel!

Thanks both for your help, got it working with the timeserial function and also doing it on the fly.

Martin