Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    62

    Unanswered: Convert time format

    Hello,

    I use "table links" to MSSQL2000 server tables in my MSACCESS database.

    In my MSSQL2000 database dates are in "time" format.

    Is there a function to transform this "time" format to usable date format?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    try:

    public function makeServerDateTime(anyAccessDateTime as date) as string
    if not isdate(anyAccessDateTime) then exit function
    makeServerDateTime = "'" & format$(anyAccessDateTime, "mm/dd/yyyy hh:mm:ss") & "'"
    end function

    stick this function in your "utilities" module and process ALL access datetimes through it when building server-SQL expressions.

    strSQL = strSQL & "WHERE mySvrDate = " & makeServerDateTime(myDate) & " etc

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ah!
    i see
    you seem want to go the other way:
    access should do this for you "naturally" with zero effort on your part.

    if you just don't want to see the time-part, set the format of your textbox to shortdate (or whatever pleases you)

    ...and if that's not it, please restate the question.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Aug 2003
    Posts
    62
    I like to transform data from time to date in the WHERE clause

    In a previous version for access (2002 sp1), I use the function cvrtfromunixtime :
    DateValue(cvrtfromunixtime([open_date]))

    I just want to know if there is a new function replacing this one in MSaccess 2003.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    aha!

    i suspect your function was home-made rather than an intrinsic access function. eitherhow, shouldnt be too difficult to make a suitable function.

    your unixtime is seconds after 31st December, 1969 ????
    access dates are days-point-fractional days after 29th December 1899
    i.e. access datetime = 0.5 is midday 30th December 1899

    ...a little bit of maths and you are done.

    izy
    Last edited by izyrider; 01-12-05 at 14:57. Reason: changed 0-time unix definition
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •