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

Posted on 2004-11-01
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
    LVL 8

    Accepted Solution

    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

    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

    LVL 8

    Expert Comment

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

    Expert Comment

    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)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    Course: MongoDB Object-Document Mapper for NodeJS

    NodeJS (JavaScript on the server) is awesome, but some developers get confused about NoSQL when it comes to working in Node with MongoDB (NoSQL database). Do you need a better explanation of how to use Node.js with MongoDB? The most popular choice is the Mongoose library.

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    877 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now