Question concerning Timestamps

[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?

 

[RuralGuy]

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.

 

[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])

 

[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])

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

 

[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])

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

 

[James A. Fortune]

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
(e-mail address removed)

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

 

[Guest]

My error - INT([YourDateTimeField])

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

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

 

[dbahooker]

doesn't converting a datetime; that is in the afternoon-- round the
number UP?

i mean seriously; am I the only one that has had this problem?

Cint(Now()-.5) = Cint(Date())

My error - INT([YourDateTimeField])

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

:

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

 

[Douglas J. Steele]

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.

 

[James A. Fortune]

Actually, that would be the DateValue() function.

Yes. My mistake. Thanks.

James A. Fortune
(e-mail address removed)

 

[Jamie Collins]

doesn't converting a datetime; that is in the afternoon-- round the
number UP?

i mean seriously; am I the only one that has had this problem?

Cint(Now()-.5) = Cint(Date())

INT() and CINT() use distinct rounding algorithms: the INT() function
rounds down and the CINT() function uses banker's rounding.

The best approach IMO is to operate on DATETIME functions using
temporal functions e.g. use DATEVALUE().

Jamie.

--

 

[dbahooker]

uh what's the difference between bankers' rounding and standard
rounding?

does it mean that something at 1/30/2007 1pm gets rounded UP to
1/31/2007?

 

[Jamie Collins]

uh what's the difference between bankers' rounding and standard
rounding?

MS products' rounding explained:
http://support.microsoft.com/default.aspx?scid=kb;en-us;196652

does it mean that something at 1/30/2007 1pm gets rounded UP to
1/31/2007?

I'd recommend you use a calendar auxilary table to define the 'start'
and 'end' date/times for each date required by your enterprise e.g. (to
one second granularity):

dt = '2007-01-31'
start_time = '2007-01-30T12:59:59'
end_time = '2007-01-31T13:00:00'

dt = '2007-02-01'
start_time = '2007-01-31T12:59:59'
end_time = '2007-02-01T13:00:00'

etc

A few decades worth of data won't take up much space (even in a mdb
<g>).

Jamie.

--

 

[dbahooker]

oh perfect

so if we're on an EVEN day then it rounds up and an odd day then it
rounds down?

i say you should subtract .5 to make it 'always round down'

 

[Jamie Collins]

so if we're on an EVEN day then it rounds up and an odd day then it
rounds down?

i say you should subtract .5 to make it 'always round down'

Then model it this way by creating some appropriate data in a calendar
auxilary table.

I think it is trying to do mathematics on temporal data (i.e. different
domains) that is causing you pain.

Jamie.

--

 

[dbahooker]

yeah I've been doing that for 10 years thanks

i dont have pain; i just think that there is some mis-information about
rounding out there

i dont think that a simple round of a date datatype is always safe