Access export date and time fields and keep certain format

Posted on 2009-07-06
Last Modified: 2013-11-27
I have a query that I am exporting and need to keep the format a certain way.
the date needs to look like this

20090707 for July 7, 2009

I have the format correct in the query but when I export it it turns to this.

7/7/2009 1:47:58 PM

Same with the time - I have it set to HHMMSS
hour minute second
and when exporting I get

1:47:58 PM

Question by:Pdeters
  • 2
LVL 17

Expert Comment

ID: 24788110
FORMAT([Table].[Field], "yyyyMMdd")
Wouldn't this work.
LVL 17

Accepted Solution

pssandhu earned 500 total points
ID: 24788135
Also, if you are exporting the result into another table then make sure the target column default format is set to yyyyMMdd. You can set this in the table design mode.

Author Closing Comment

ID: 31600302
I put in the query as it is not pulling a date but using today's date



Expert Comment

ID: 24788312
You can also try

date_format([table].[fld], '%Y%m%d')
#should print "yyyymmdd" 20090631


date_format([table].[fld], '%h%i%s')
#should print "hhmmss"

query usage:
$query = "SELECT date_format(datefld , '%Y%m%d') FROM ourTable";

Hope this helps.

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Access application from Task Scheduler 6 36
Unable to save view in SSMS 21 60
MS Access 03, TransferText, decimal places 8 47
append to an ms access field 6 26
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

862 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

23 Experts available now in Live!

Get 1:1 Help Now