Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date/Time calculation in Query (9.0)

    I have two fields in my table, START_DATE and QUANTITY. I have a start_date field in the format of '9/12/2005 3:03:19 PM' and need to mulitply this by a number represented by quantity field. I need to return the result in my select statement into a new value called 'ENDDATE', but return it as date/time value, how can this be accomplished?

    ex. SELECT START_DATE, QUANTITY, TO_DATE(START_DATE, YYYY-MM-DD HH:SS:MM) * QUANTITY as ENDDATE
    FROM CMF

    This does not work for me as I am not sure if I need to create a julian date somehow with a time value representation.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date/Time calculation in Query (9.0)

    Multiplying a date by a number doesn't make sense, and TO_DATE is not an Access or SQL function. Perhaps you want to add a number of days to Start_Date? In that case. you can use

    ...,[Start_Date]+[Quantity] AS EndDate ...

    Otherwise, please explain what you want to accomplish.

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time calculation in Query (9.0)

    Hello Hans. Well, using SQL I am trying to convert the date to a number in order to multiply the date by a number. Then I need to return this result in the select statement, but convert the number result into a date/time value as it was before.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date/Time calculation in Query (9.0)

    But why in heaven's name do you want to multiply a date by a number? The result is not likely to represent a valid date any more.

  5. #5
    New Lounger
    Join Date
    Jul 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time calculation in Query (9.0)

    Well, our database does not have an end date for when a phone call ended. We have the start date of the phone call and how many seconds it lasted. I am attempting to add the seconds it lasted to the start date to get the end date as to when the call ended.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date/Time calculation in Query (9.0)

    If the Start_Date is a date/time field, it is stored as a number with 1 day as unit (the time is the fractional part). If you want to add a number of seconds, you can use
    <code>
    DateAdd("s",[Quantity],[Start_Date]) AS EndDate
    </code>
    or even (since there are 86400 seconds in a day)
    <code>
    [Start_Date]+[Quantity]/86400 AS EndDate</code>

  7. #7
    New Lounger
    Join Date
    Jul 2004
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date/Time calculation in Query (9.0)

    The second one worked. SQL for some reason does not recognize DateAdd.

    Thank you for your help Hans. I guess it was simpler than it looked. I have been working at this simple statement for a day now. I have a lot to learn.

    Mark

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date/Time calculation in Query (9.0)

    Do you mean you are using SQL Server? That was not clear to me, since SQL is also the query language used by Access. DateAdd is a VBA function, Access recognizes them, but SQL Server doesn't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •