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?  
JoseDavilaAsked:
Who is Participating?
 
SimonLarsenCommented:
Depends which sql.

Access: Use format([DateField], "mm/dd/yy hh:nn:ss")

in MS SQL: use Convert (varchar(17), getdate(), 01) + ' ' + Convert (varchar(17), getdate(), 108)

(for MS SQL there may be a quicker one, I don't use that format much. The format is the 3rd argument (01 and 108)
0
 
JoseDavilaAuthor Commented:
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

0
 
SimonLarsenCommented:
is start_time a date field? if so ditch the cdbl and /86400. You don't need them....
0
 
SimonLarsenCommented:
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)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.