Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-11-01
Medium Priority
Last Modified: 2008-03-03
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?  
Question by:JoseDavila
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Accepted Solution

SimonLarsen earned 1500 total points
ID: 12467895
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)

Author Comment

ID: 12468404
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


Expert Comment

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

Expert Comment

ID: 12468878
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)

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question