using ms access to convert ms access & sql server date time to unix time stamp

using ms access to convert ms access & sql server date time to unix time stamp

Post by jjj092 » Fri, 27 Jan 2006 03:57:55


I'd like to use access to convert a sql server table (using an ODBC
connection) to a MYsql table (again using an odbc connection) and I
have all the field mappings worked out except for the time stamp.

is there a function within MS Access to do the conversion, if not - is
there a formula or an activeX plug-in that will do this for me?

thanks in advance for any help you may provide.

- jeff
 
 
 

using ms access to convert ms access & sql server date time to unix time stamp

Post by Marshall B » Fri, 27 Jan 2006 04:11:08


There may very well be a better way using TSQL or some such
server side calculation, but in Access you can use the date
functions to do this. Actually, I think that the SQL Server
dates are automatically converted to Access dates by the
time Access sees them. In this case (I think MySQL uses the
number of seconds since 1/1/70 as its base date), so the
expression would be something along these lines:
DateDiff("s", #1/1/70#, datefield)

--
Marsh
MVP [MS Access]

 
 
 

using ms access to convert ms access & sql server date time to unix time stamp

Post by jjj092 » Fri, 27 Jan 2006 05:08:50

thank you - that works perfectly - all I did was substiture the
[sqlserverdbname].[fieldname] for datefield and it was perfect!

thank you so much!!!
 
 
 

using ms access to convert ms access & sql server date time to unix time stamp

Post by John Spenc » Fri, 27 Jan 2006 05:20:21


IN SQL
timestamp data type
A SQL Server system data type that is a monotomically increasing counter
whose values are always unique within a database.

Basically, it is something like an autonumber that changes when a record is
created or changed - I once heard it called a row version number.