Store Pure Date and pure time

[Kiran Thomas]

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

[Dean Earley]

> 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

[Michael Cole]

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...

[Arthur Wood]

"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

[Daniel_Johns]

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!)

[Tapiwa]

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!)
>
>

[Tapiwa]

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!)
>
>

[Daniel_Johns]

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!