Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Question concerning Timestamps

Discussion in 'Information Technology' started by Bob, Jul 28, 2006.

  1. Bob

    Bob
    Expand Collapse
    Guest

    In MS Excel there is a Text to columns function that allows you to cut off
    timestamps on Date/Time fields so you can derive calculations from them. Is
    there a similiar function in MS Access that allows you to utilize the date
    field in a timestamp without using the rest of it?
     
  2. Loading...

    Similar Threads Forum Date
    Question Concerning Violence Questions and Answers Oct 30, 2009
    Question About GGS Questions and Answers Aug 28, 2016
    Marriage And Discrimination Related Questions Blogs Mar 21, 2016
    Sikhi Questioning Faith Sikh Sikhi Sikhism Mar 3, 2016
    Entering into Sikhism: Questions on Reht Maryada. Blogs Oct 22, 2015

  3. RuralGuy

    RuralGuy
    Expand Collapse
    Guest

    On Wed, 28 Jun 2006 11:56:02 -0700, Bob <Bob@discussions.microsoft.com> wrote:

    >In MS Excel there is a Text to columns function that allows you to cut off
    >timestamps on Date/Time fields so you can derive calculations from them. Is
    >there a similiar function in MS Access that allows you to utilize the date
    >field in a timestamp without using the rest of it?

    Maybe something in this link will suit your needs.

    A plethora of date functions http://www.pacificdb.com.au/MVP/Code/Dates.htm
    _______________________________________________
    hth - RuralGuy (RG for short)
    Please post to the NewsGroup so all may benefit.
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    The datetime is stored as a decimal number. The whole number part is the
    date and the fraction is the decimal part of a day. The time of 6 AM is a
    quarter of a day and is stored as 0.25 in the field with the date.

    So just use the Absolute function to remove the decimal part.
    ABS([YourDateTimeField])

    "Bob" wrote:

    > In MS Excel there is a Text to columns function that allows you to cut off
    > timestamps on Date/Time fields so you can derive calculations from them. Is
    > there a similiar function in MS Access that allows you to utilize the date
    > field in a timestamp without using the rest of it?
     
  5. Bob

    Bob
    Expand Collapse
    Guest

    "KARL DEWEY" wrote:

    > The datetime is stored as a decimal number. The whole number part is the
    > date and the fraction is the decimal part of a day. The time of 6 AM is a
    > quarter of a day and is stored as 0.25 in the field with the date.
    >
    > So just use the Absolute function to remove the decimal part.
    > ABS([YourDateTimeField])
    >
    > "Bob" wrote:
    >
    > > In MS Excel there is a Text to columns function that allows you to cut off
    > > timestamps on Date/Time fields so you can derive calculations from them. Is
    > > there a similiar function in MS Access that allows you to utilize the date
    > > field in a timestamp without using the rest of it?


    I tried this and it didn't work the way I was hoping. the Abs function
    didn't take off the decimal part of the timestamp, rather it just formatted
    the field to a number field. I wonder if I just do right function to cut off
    the field if that would work
     
  6. Bob

    Bob
    Expand Collapse
    Guest

    "KARL DEWEY" wrote:

    > The datetime is stored as a decimal number. The whole number part is the
    > date and the fraction is the decimal part of a day. The time of 6 AM is a
    > quarter of a day and is stored as 0.25 in the field with the date.
    >
    > So just use the Absolute function to remove the decimal part.
    > ABS([YourDateTimeField])
    >
    > "Bob" wrote:
    >
    > > In MS Excel there is a Text to columns function that allows you to cut off
    > > timestamps on Date/Time fields so you can derive calculations from them. Is
    > > there a similiar function in MS Access that allows you to utilize the date
    > > field in a timestamp without using the rest of it?


    3/9/2006 8:18:00 PM

    thats an example of the timestamp I am dealing with
     
  7. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Bob wrote:
    > In MS Excel there is a Text to columns function that allows you to cut off
    > timestamps on Date/Time fields so you can derive calculations from them. Is
    > there a similiar function in MS Access that allows you to utilize the date
    > field in a timestamp without using the rest of it?


    Karl probably meant to use the Int() function to extract the date part.
    The answers posted so far rely on how the date is stored internally.
    Instead, it is preferable to use the Date() function to get only the
    date information from the timestamp.

    James A. Fortune
    MPAPoster@FortuneJames.com

    Recipes for clones of brandname/restaurant foods:
    http://www.topsecretrecipes.com/recipes.asp
     
  8. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    My error - INT([YourDateTimeField])


    "Bob" wrote:

    >
    >
    > "KARL DEWEY" wrote:
    >
    > > The datetime is stored as a decimal number. The whole number part is the
    > > date and the fraction is the decimal part of a day. The time of 6 AM is a
    > > quarter of a day and is stored as 0.25 in the field with the date.
    > >
    > > So just use the Absolute function to remove the decimal part.
    > > ABS([YourDateTimeField])
    > >
    > > "Bob" wrote:
    > >
    > > > In MS Excel there is a Text to columns function that allows you to cut off
    > > > timestamps on Date/Time fields so you can derive calculations from them. Is
    > > > there a similiar function in MS Access that allows you to utilize the date
    > > > field in a timestamp without using the rest of it?

    >
    > 3/9/2006 8:18:00 PM
    >
    > thats an example of the timestamp I am dealing with
     
  9. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    "James A. Fortune" <jimfortune@compumarc.com> wrote in message
    news:O4wHrtumGHA.4684@TK2MSFTNGP03.phx.gbl...
    > Bob wrote:
    >> In MS Excel there is a Text to columns function that allows you to cut
    >> off timestamps on Date/Time fields so you can derive calculations from
    >> them. Is there a similiar function in MS Access that allows you to
    >> utilize the date field in a timestamp without using the rest of it?

    >
    > Karl probably meant to use the Int() function to extract the date part.
    > The answers posted so far rely on how the date is stored internally.
    > Instead, it is preferable to use the Date() function to get only the date
    > information from the timestamp.


    Actually, that would be the DateValue() function.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no private e-mails, please)
     
  10. James A. Fortune

    James A. Fortune
    Expand Collapse
    Guest

    Douglas J. Steele wrote:
    > "James A. Fortune" <jimfortune@compumarc.com> wrote in message
    > news:O4wHrtumGHA.4684@TK2MSFTNGP03.phx.gbl...
    >
    >>Bob wrote:
    >>
    >>>In MS Excel there is a Text to columns function that allows you to cut
    >>>off timestamps on Date/Time fields so you can derive calculations from
    >>>them. Is there a similiar function in MS Access that allows you to
    >>>utilize the date field in a timestamp without using the rest of it?

    >>
    >>Karl probably meant to use the Int() function to extract the date part.
    >>The answers posted so far rely on how the date is stored internally.
    >>Instead, it is preferable to use the Date() function to get only the date
    >>information from the timestamp.

    >
    >
    > Actually, that would be the DateValue() function.
    >


    Yes. My mistake. Thanks.

    James A. Fortune
    MPAPoster@FortuneJames.com
     

Share This Page