asked on

Converting SQL or Access Date to mm-dd-yyyy hh-mm-ss

In MS Access, I have created a Linked TABLE. This table is linked to a table in an MS SQL table.

One of the columns is called StartTime.

For the date 10/29/04 08:31:13 the table shows a value of 1099063873. I need the value after I query the table be shown as

mm/dd/yy hh:mm:ss

What is the function that I can use in a SQL statement when querying the linked table that returns this format?

ASKER CERTIFIED SOLUTION

SimonLarsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JoseDavila

ASKER

The way I got it to work was by dividing the number by 86400 - number of seconds in a day - then I applied your suggestion -> format([DateField], "mm/dd/yy hh:nn:ss")

It ended up looking like:

Format(CDbl(([dbo_inspectiondata Macro T1].start_time)/86400),"mm/dd/yy hh:nn:ss") AS StarTime

SimonLarsen

is start_time a date field? if so ditch the cdbl and /86400. You don't need them....

SimonLarsen

oh and if you want to get fancy, change the linked table to a pass through query and put the view onto the sql box, it ought to run quicker that way, then you'llw ant to use the other formatting trick:

select Convert (varchar(17), [Start_date], 01) + ' ' + Convert (varchar(17), [Start_date], 108)
from [inspectiondata Macro T1]

(When using Access backended onto sql linked tables are often the slowest way of doing things. A stored proc or view will be better)