Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Access: convert data type from integer to date/time


  • Please log in to reply

#1
wildocean

wildocean

    New Member

  • Member
  • Pip
  • 3 posts
Hi,

I have two tables that I would like to link via their 'time' columns. One table has been imported, and the data type for the 'time' column is in date/time format. Unfortunately, the table that is already in the database has its 'time' column as an integer (of format 00\:00). Since they're in different data types, I can't build a relationship between them for my queries, and I've had no luck converting the date/time data to integers or vice versa. Any suggestions would be greatly appreciated!

Thanks for your time
  • 0

Advertisements


#2
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Not sure what you mean by "I would like to link via their 'time' columns". Link in what way?

"the table that is already in the database has its 'time' column as an integer (of format 00\:00)."
Can you provide specific examples of the data from both tables.
  • 0

#3
wildocean

wildocean

    New Member

  • Topic Starter
  • Member
  • Pip
  • 3 posts
I have two tables that I want to build a relationship between. One has the columns date, time, and much more data. The other table has date, time, and value. For all of the records in the first table, I'd like to have the values from the second table (i.e. for each specific date and time, there is one value). In the relationships, I can therefore join the dates across each table, but it won't let me join the times because of a data mismatch. Is that more clear? Thanks!
  • 0

#4
bomtond

bomtond

    New Member

  • Member
  • Pip
  • 4 posts
First time, so please be patient with me. This is not the most eloquent way of doing it, but it's effective. What you can do is make a column in each table that can be used for your joins with the same datatype and number.

Your first table with the datetime time, it actually is a number, a double somewhere between zero and one. Access/Excel/Yadda store datetimes as a double with 1=1day. Your time is actually being stored as the (number of seconds in your time)/86400 (the number of seconds in a day). To see this, create a column in this table, and populate it with an update query with cdbl([time]). For my example, the time 01:23 comes out to the double .057638888889.

Your second table is the tricky one. With that formatting, you are actually storing a time liek 15:45 as an interger 1545. But in order to get it to match, we have to seperate the hour from the minute, figure out how many seconds that is, then divide it by 86400. I did it with the following formula: ((Int([time]/100)*3600)+(60*([time] Mod 100)))/86400. Create a column in your second table and populate it with this formula.

So basically, you join on these two create columns, or CDbl([table1].[time])=((Int([table2].[time]/100)*3600)+(60*([table2].[time] Mod 100)))/86400

I got it to work, but like I said, there are other ways. You could take the double from the CDbl and multiply by 86400, then convert that integer of number of seconds into the integer version of table 2. Or you could do a CDate conversion on that big long expression in table 2 and link that to the time in table 1.

Hope that helped.
  • 0

#5
agromerchant

agromerchant

    New Member

  • Member
  • Pip
  • 1 posts
Hi Geeks users.....I saw your CONVERT NUMBER TO DATE but, I can not convert 3 fields from 1 table. Day: 28, Month: 9, Year: 2009.
I have joined those fields with &, but is not possible to convert (cdate)
For exmaple, I have 2892009, which is September 28, 2009.
So please, I want to convert those fields is date.....how is it?
  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP