Store Pure Date and pure time


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Store Pure Date and pure time

  1. #1
    Kiran Thomas Guest

    Store Pure Date and pure time


    hi
    I want to writes data into datetime fields as pure dates, or pure time.
    Currently, we store
    date as 01/01/1900 08:00:00. but now we want to store data is written as
    08:00:00. Dates are written as
    09/11/2002.(both in datetime field, not as string)
    RGDS
    kiran


  2. #2
    Dean Earley Guest

    Re: Store Pure Date and pure time

    > I want to writes data into datetime fields as pure dates, or pure time.
    > Currently, we store
    > date as 01/01/1900 08:00:00. but now we want to store data is written as
    > 08:00:00. Dates are written as
    > 09/11/2002.(both in datetime field, not as string)


    You cant unless the DB supports date or time fields. I know mysql supports this, but I don't know about mssql. Also, once they are
    read into VB, they will be datetime fields anyway.

    --
    Dean Earley (dean.earley@icode.co.uk)
    i-Catcher Development Team

    iCode Systems



  3. #3
    Michael Cole Guest

    Re: Store Pure Date and pure time

    Kiran Thomas wrote:
    > hi
    > I want to writes data into datetime fields as pure dates, or pure
    > time. Currently, we store
    > date as 01/01/1900 08:00:00. but now we want to store data is written
    > as 08:00:00. Dates are written as
    > 09/11/2002.(both in datetime field, not as string)


    I think you will find that they are actually stored as floating point
    numbers...



  4. #4
    Arthur Wood Guest

    Re: Store Pure Date and pure time


    "Michael Cole" <michael.cole@hansen.com> wrote:
    >Kiran Thomas wrote:
    >> hi
    >> I want to writes data into datetime fields as pure dates, or pure
    >> time. Currently, we store
    >> date as 01/01/1900 08:00:00. but now we want to store data is written
    >> as 08:00:00. Dates are written as
    >> 09/11/2002.(both in datetime field, not as string)

    >
    >I think you will find that they are actually stored as floating point
    >numbers...
    >
    >

    what is actually stored (in the database) is a Double Precision FLOATING
    point number where the Integer part of the Number (to the LEFT of the Decimal
    pont) is the count of the number of since since Dec 31,1899 and the fractional
    part of the number (to the right of the decimal point) is the fraction of
    one full day (accurate to 1 second), so that NOON would be represented as
    0.5.

    Using this, Now (Sept 12,2002 at 10:18:00 AM EDT) would be

    37512.4291666667

    Arthur Wood

  5. #5
    Daniel_Johns Guest

    Re: Store Pure Date and pure time

    I'm not sure in SQL Server, but if you're using Access DB (2000), you can
    store it in a different date/time format with the date/time data field type.
    as my example shows:

    'rs is the recordset and MyDateTimefield is the date/time field

    dim tmpLongTime as Date
    dim tmpShortDate as Date

    'code...code...code

    rs.addnew

    tmpLongTime = FormatDateTime(Now, vbLongTime) 'example 10:30:24
    tmpShortDate = FormatDateTime(Now, vbShortDate) 'example 09/11/2002

    rs!MyDateTimeField = tmpLongTime
    'or tmpShortDate if you want the ShortDate to be stored

    rs.Update

    'code...code...code

    If this is what you meant, well hope it helps you. but if it isn't...i got
    you wrong! lol! (Access DB only, not SQL SERVER!)



  6. #6
    Tapiwa Guest

    Re: Store Pure Date and pure time


    Whilest Daniel is correct, there are some areas that he moght have over looked:
    (1) Format returns a string which in some cases might cause an error
    (2) vblongtime and vblong date are dependent on the specifications made in
    the reginal setting of the computer

    so, instead of format(now,vbshortdate)
    I prefere the implicit specification: format(now,"dd-mm-yy") which lets gives
    you control ove the exact date format to be used.

    to convert the result to a pure date then use the cdate(arg) function - remmber
    that you can also use cdate to convert time.

    you should smile because SQL server has as datetime field type hence it
    will be stored as a pure date or time
    ___________________________________________________________________________

    "Daniel_Johns" <Daniel_Johns@mail2world.com> wrote:
    >I'm not sure in SQL Server, but if you're using Access DB (2000), you can
    >store it in a different date/time format with the date/time data field type.
    >as my example shows:
    >
    >'rs is the recordset and MyDateTimefield is the date/time field
    >
    >dim tmpLongTime as Date
    >dim tmpShortDate as Date
    >
    >'code...code...code
    >
    >rs.addnew
    >
    >tmpLongTime = FormatDateTime(Now, vbLongTime) 'example 10:30:24
    >tmpShortDate = FormatDateTime(Now, vbShortDate) 'example 09/11/2002
    >
    >rs!MyDateTimeField = tmpLongTime
    >'or tmpShortDate if you want the ShortDate to be stored
    >
    >rs.Update
    >
    >'code...code...code
    >
    >If this is what you meant, well hope it helps you. but if it isn't...i got
    >you wrong! lol! (Access DB only, not SQL SERVER!)
    >
    >



  7. #7
    Tapiwa Guest

    Re: Store Pure Date and pure time


    While Daniel is correct, there are some areas that he might have over looked:
    (1) Format returns a string which in some cases might cause an error
    (2) vblongtime and vblong date are dependent on the specifications made in
    the reginal setting of the computer

    so, instead of format(now,vbshortdate)
    I prefere the implicit specification: format(now,"dd-mm-yy") which gives
    you control over the exact date format to be used.

    to convert the result to a pure date then use the cdate(arg) function - rember
    that you can also use cdate to convert time.

    you should smile because SQL server has as datetime field type hence it
    will be stored as a pure date or time
    ___________________________________________________________________________

    "Daniel_Johns" <Daniel_Johns@mail2world.com> wrote:
    >I'm not sure in SQL Server, but if you're using Access DB (2000), you can
    >store it in a different date/time format with the date/time data field type.
    >as my example shows:
    >
    >'rs is the recordset and MyDateTimefield is the date/time field
    >
    >dim tmpLongTime as Date
    >dim tmpShortDate as Date
    >
    >'code...code...code
    >
    >rs.addnew
    >
    >tmpLongTime = FormatDateTime(Now, vbLongTime) 'example 10:30:24
    >tmpShortDate = FormatDateTime(Now, vbShortDate) 'example 09/11/2002
    >
    >rs!MyDateTimeField = tmpLongTime
    >'or tmpShortDate if you want the ShortDate to be stored
    >
    >rs.Update
    >
    >'code...code...code
    >
    >If this is what you meant, well hope it helps you. but if it isn't...i got
    >you wrong! lol! (Access DB only, not SQL SERVER!)
    >
    >



  8. #8
    Daniel_Johns Guest

    Re: Store Pure Date and pure time

    Absolutely Tapiwa, but i never encounter an error regarding such issue, and
    that's why im using vbLongTime and vbLongDate for generality, if you're into
    explicit, well Tapiwa is fairly right. But in most cases, American standard
    time is general, and thought that my previous post was an-error free (though
    im using it for years!) good day!



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center