[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

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?  
0
JoseDavila
Asked:
JoseDavila
  • 3
1 Solution
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now