Converting DATE & Time

[johnseito]

How do I convert the below date time into separate date and time and use the date to minus today's date and current time?



9/22/2013 11:03:00 PM

I tried this code below and it doesn't work.

SELECT bid.bidtime,(date()- (format ( [bidtime], "mm/dd/yy"))),  Date() AS Expr1FROM bid;

[June7]

Why break them up? Use DateDiff() function. Access Help has more info about the function.

SELECT *, DateDiff("d", Date(), [bidtime]) AS NewDate FROM bed;

[Bob Fitz]

johnsieto
From the code you posted I have assumed that your table is called “bid” and your Date/Time field is called “bidtime”. The following solution will give a query in which there is one column headed “TimeElapsed” which shows the years, months, days, hours, minutes, seconds from the date and time held in “bidtime” to Now().

Copy the following code to a standard (general) module.

 '***************** Code Start **************
Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
  Optional ShowZero As Boolean = False, Optional iYears, Optional iMonths, Optional iDays, _
  Optional iHours, Optional iMinutes, Optional iSeconds) As Variant
'Author:    ) Copyright 2001 Pacific Database Pty Limited
'           Graham R Seach MCP MVP gseach@pacificdb.com.au
'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593
'           This code is freeware. Enjoy...
'           (*) Amendments suggested by Douglas J. Steele MVP
'
'Description:   This function calculates the number of years,
'               months, days, hours, minutes and seconds between
'               two dates, as elapsed time.
'
'Inputs:    Interval:   Intervals to be displayed (a string)
'           Date1:      The lower date (see below)
'           Date2:      The higher date (see below)
'           ShowZero:   Boolean to select showing zero elements
'
'Outputs:   On error: Null
'           On no error: Variant containing the number of years,
'               months, days, hours, minutes & seconds between
'               the two dates, depending on the display interval
'               selected.
'           If Date1 is greater than Date2, the result will
'               be a negative value.
'           The function compensates for the lack of any intervals
'               not listed. For example, if Interval lists "m", but
'               not "y", the function adds the value of the year
'               component to the month component.
'           If ShowZero is True, and an output element is zero, it
'               is displayed. However, if ShowZero is False or
'               omitted, no zero-value elements are displayed.
'               For example, with ShowZero = False, Interval = "ym",
'               elements = 0 & 1 respectively, the output string
'               will be "1 month" - not "0 years 1 month".
' Modified 10/13/03 by Pat Hartman to also output separate date part fields
 
On Error GoTo Err_Diff2Dates
 
   Dim booCalcYears As Boolean
   Dim booCalcMonths As Boolean
   Dim booCalcDays As Boolean
   Dim booCalcHours As Boolean
   Dim booCalcMinutes As Boolean
   Dim booCalcSeconds As Boolean
   Dim booSwapped As Boolean
   Dim dtTemp As Date
   Dim intCounter As Integer
   Dim lngDiffYears As Long
   Dim lngDiffMonths As Long
   Dim lngDiffDays As Long
   Dim lngDiffHours As Long
   Dim lngDiffMinutes As Long
   Dim lngDiffSeconds As Long
   Dim varTemp As Variant
 
 '  Const INTERVALS As String = "dmyhns"
   Const INTERVALs2 As String = "dmyhns"
 
'Check that Interval contains only valid characters
   Interval = LCase$(Interval)
   For intCounter = 1 To Len(Interval)
      If InStr(1, INTERVALs2, Mid$(Interval, intCounter, 1)) = 0 Then
         Exit Function
      End If
   Next intCounter
 
'Check that valid dates have been entered
   If Not (IsDate(Date1)) Then Exit Function
   If Not (IsDate(Date2)) Then Exit Function
 
'If necessary, swap the dates, to ensure that
'Date1 is lower than Date2
   If Date1 > Date2 Then
      dtTemp = Date1
      Date1 = Date2
      Date2 = dtTemp
      booSwapped = True
   End If
 
   Diff2Dates = Null
   varTemp = Null
 
'What intervals are supplied
   booCalcYears = (InStr(1, Interval, "y") > 0)
   booCalcMonths = (InStr(1, Interval, "m") > 0)
   booCalcDays = (InStr(1, Interval, "d") > 0)
   booCalcHours = (InStr(1, Interval, "h") > 0)
   booCalcMinutes = (InStr(1, Interval, "n") > 0)
   booCalcSeconds = (InStr(1, Interval, "s") > 0)
 
'Get the cumulative differences
   If booCalcYears Then
      lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _
              IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)
      Date1 = DateAdd("yyyy", lngDiffYears, Date1)
   End If
 
   If booCalcMonths Then
      lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _
              IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)
      Date1 = DateAdd("m", lngDiffMonths, Date1)
   End If
 
   If booCalcDays Then
      lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _
              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)
      Date1 = DateAdd("d", lngDiffDays, Date1)
   End If
 
   If booCalcHours Then
      lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _
              IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)
      Date1 = DateAdd("h", lngDiffHours, Date1)
   End If
 
   If booCalcMinutes Then
      lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _
              IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)
      Date1 = DateAdd("n", lngDiffMinutes, Date1)
   End If
 
   If booCalcSeconds Then
      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))
      Date1 = DateAdd("s", lngDiffSeconds, Date1)
   End If
 
   If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then
      varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")
   End If
 
   If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then
      If booCalcMonths Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")
      End If
   End If
 
   If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then
      If booCalcDays Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")
      End If
   End If
 
   If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then
      If booCalcHours Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")
      End If
   End If
 
   If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then
      If booCalcMinutes Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")
      End If
   End If
 
   If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then
      If booCalcSeconds Then
         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _
                   lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")
      End If
   End If
 
   If booSwapped Then
      varTemp = "-" & varTemp
   End If
 
   Diff2Dates = Trim$(varTemp)
   iYears = lngDiffYears
   iMonths = lngDiffMonths
   iDays = lngDiffDays
   iHours = lngDiffHours
   iMinutes = lngDiffMinutes
   iSeconds = lngDiffSeconds
 
End_Diff2Dates:
   Exit Function
 
Err_Diff2Dates:
   Resume End_Diff2Dates
 
End Function
'************** Code End *****************

Create a query with the following SQL statement:

 SELECT Diff2Dates("ymdhns",[bidtime],Now()) AS TimeElapsed
FROM bid;

Unfortunately, I can take NO credit for the code which I have copied from the attached db which is well worth taking a look at, as it has many useful date and time functions.

[johnseito]

Ok, thanks, that works with the datediff. I see it as this example.

datediff("d",[bidtime],endDATE)

but do you know if it would work with time,
maybe something like this

datediff("h:n:s", [bidtime], endTIME)

endTIME is only time such as this 8:03 am and bidtime is date and time such as this 9/21/13 11:01:00 am

Thanks

[June7]

You should review the Access Help about DateDiff.

[johnseito]

It looks like every time I search in Access library it goes to the WEB rather than Access internal library. How do I search the explanation from the library.

I have tried all sorts of ways to get the days, hours, minutes and seconds difference between two date/time. The out come has always seems to have an error.
These are what I have tried below. What I am trying to do is find the difference between two date/time, the number of days, hours, minutes, and seconds difference.

I have no problem setting in the WHERE clause to limit only where one date is greater than the other. If the first option doesn't work I will opt for the second.
I think the first is doable, I just wasn't able to figure it out. I have looked on the web and see none similar to what I am looking for.

datediff("d.hh.n.s",[bidtime],[ENDdate])
datediff("d:hh:n:s",[bidtime],[ENDdate])
datediff("d","hh","n","s",[bidtime],[ENDdate])

[Bob Fitz]

Have you tried the solution I offered in post #3

[johnseito]

Have you tried the solution I offered in post #3

Hi Bob,

I did looked at it, but not all of it. I noticed your example is mostly done in access VBA rather than access SQL.
I was looking to see if it can be done in soley SQL. The second thing about it, is my first questions was how can
a date/time combo example as this 10/4/13 10:47:53 pm, calculate the time difference of this, example 10:48:53 pm.

The example you provide didn't resolve this, because the code created doesn't know the date of the second time or it doesn't exclude
the date of the first, so it can be calculated. So to make it less difficult and life easier I made the two date the same with date and time on my table to
resolve this calculating issue.


When I put one date/time and the other time, your example gave a very big difference in minutes, the equivalence of 114 years. Here is a picture attached.
Courtesy to the developer who created this.

The main issue is your example did that calculation using VBA rather than SQL which was what I am looking for.

[June7]

http://office.microsoft.com/en-us/ac...001228811.aspx

DateDiff("s", enddate, startdate) will return total seconds

DateDiff("n", enddate, startdate) will return total minutes

DateDiff("h", enddate, startdate) will return total hours

DateDiff("d", enddate, startdate) will return total days

etc.

If you want to represent the elapsed time as: 1:23:52 i.e. 1 hr 23 min 52 sec, that will require some code to calculate each time part and concatenate.

x = elapsed time in seconds resulting from the DateDiff calc

Int(x/3600) & ":" & Int((x mod 3600) / 60) & ":" Format(((x mod 3600) / 60 - Int((x mod 3600) / 60))*100,"00")