date to integer

[Bre-x]

How to you convert a date to a integer?

Example
6/14/2006 1:57:37 PM to 1150308000

I have seen it on other languages like PHP

Thnks for your help

Bre-x

 

[John Vinson]

How to you convert a date to a integer?

Example
6/14/2006 1:57:37 PM to 1150308000

I have seen it on other languages like PHP

I was curious enough to see if I could figure out what that date and
that number have in common.

I'm GUESSING that the number was a close-but-not-exact count of
seconds since midnight, January 1, 1970:

?datediff("s", #1/1/1970#, #6/14/2006 1:57:37 PM#)
1150293457

If that's the rationale, then you can use

DateDiff("s", #1/1/1970#, [datefield])

in a Query.

John W. Vinson[MVP]

 

[Guest]

I could not reproduce converting 6/14/2006 1:57:37 PM to 115030800.
You basic question is unachievable, because dates are not carried as
integers. Assuming 6/14/2006 1:57:37 PM is a text value, you can convert it
to a date with
dtmTheDAte = CDate("6/14/2006 1:57:37 PM ")
Then, to conver that to a numeric value:
dblDateNumber = CDbl(dtmTheDate)
dblDateNumber will now contain 38882.5816782407

 

[Bre-x]

Sorry I was looking to the wrong information. This is how it is:

Start Time:08:15:00 - Thursday 15 June 2006 = 1150380900
End Time:09:15:00 - Thursday 15 June 2006 = 1150384500

if it can be done on PHP, I dont see why not on VBA

Regards,

Bre-x

 

[John Vinson]

Sorry I was looking to the wrong information. This is how it is:

Start Time:08:15:00 - Thursday 15 June 2006 = 1150380900
End Time:09:15:00 - Thursday 15 June 2006 = 1150384500

if it can be done on PHP, I dont see why not on VBA

Regards,

It can, and I answered two days ago explaining how.

To reiterate:

DateDiff("s", #1/1/1970#, [Start Time]) + <your timezone correction>

I'm not knowledgable about PHP but your assumption that all other
software (such as Access) should and must use the same arbitrary date
format as PHP is a bit presumptuous.

<posted and emailed since you might not be seeing my posts in the
newsgroup>

John W. Vinson[MVP]

 

[Guest]

Different environments carry dates in different formats. Back in the old
days of Basic, there was no date data type. You had to use either numbers or
text and do all your own date math. Thats when we old timers had to walk 5
miles to school barefoot in the snow and it was up hill both ways

Anyway, what, exactly are you trying to accomplish? I'm sure there is an
easy way to get the result you need in VBA.

 

[Bre-x]

I wasnt trying to be "presumptuous". I have been working with MS Access for
a few years already and posting to this user group as well
So far none of my questions/problems have been unanswer/solve (Thank you
very much to all)

We are using a software called Meeting Room Booking System
http://sourceforge.net/projects/mrbs/
I would like to populate the tables (MySQL) using MS Access

Agains thnks to anyone how answer this posting.

Bre-x

 

[John Vinson]

Agains thnks to anyone how answer this posting.

I presume the answer I've posted twice now doesn't work to your
satisfaction. Could you explain what it's not doing that you want
done?

John W. Vinson[MVP]

 

[Albert D.Kallal]

How to you convert a date to a integer?

Example
6/14/2006 1:57:37 PM to 1150308000

Actually, you can do this with a date, but NOT a date + time

The above is NOT a date, but a date + time!!!!

Remember, in ms-access, you return the current date with the date()
function.

If you want date + time, then you use "now()" function.

So, you can NOT convert the above time + date into a integer value. The
reason why this is so is because access internal dates are integers, but
the time portion is actually fraction part! That fraction part is of course
24 hours. So, in fact, internal dates are double floating numbers.

So, eg: today is

6/15/2006

? clng(date())
38883

(note that we have to use a long integer, since the value is > 32000)

However, lets convert a full date + time to a double value (with the
fraction part)

? cdbl(#06/15/2006 12:00:00#)
38883.5

Note how the time part is a fractional part. So, with 1/2 day, you get .5

I suspect you considered the
possibly that ms-access uses the SAME internal format as what Unix has
(the UNIX time format is full integer, and is defined as the number of
seconds from Jan 1st, 1970).

So, perhaps you need to be able to convert from UNIX time format to
ms-access time format?

Or, perhaps you want to convent a ms-access time/date value to a UNIX
integer
time value?

The UNIX date/time is the number of seconds from

Jan 01, 1970.


So, we should be able to simply add seconds to this date.


ms-access is full of functions, and we just need to add seconds from that
date till today!!

? datediff("s",#01/01/1970#, #06/15/2006#)
1150329600


? datediff("s",#01/01/1970#, #06/15/2006 00:00:01#)
1150329601

In your case, we go

? datediff("s",#01/01/1970#, #06/14/2006 1:57:37 PM#)
1150293457

The above seems to return a different value then what you posted. (so,
either you number is wrong, or my starting date for UNIX is wrong).

So, you can well convert a ms-access date into a integer value that
represents a UNIX date (which I believe mysql uses).

So, you can't represent a ms-access date + time as a integer in
ms-access. However, you an most certainly convert it to a double precision
value. And, you most certainly can convert the value to what UNIX uses
(number of seconds from 1970).

 

[John Vinson]

? datediff("s",#01/01/1970#, #06/15/2006 00:00:01#)
1150329601

In your case, we go

? datediff("s",#01/01/1970#, #06/14/2006 1:57:37 PM#)
1150293457

The above seems to return a different value then what you posted. (so,
either you number is wrong, or my starting date for UNIX is wrong).

It's off by exactly six hours. My take is that the date/time value is
in Central Standard Time and that the UNIX standard is based on
Universal Time, so that a timezone correction is needed.

The OP has so far declined to acknowledge my repeated answers, or to
explain the rationale of the date. I give up, I've done my best.

John W. Vinson[MVP]