Solved

Access export date and time fields and keep certain format

Posted on 2009-07-06
4
196 Views
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

0
Comment
Question by:Pdeters
  • 2
4 Comments
 
LVL 17

Expert Comment

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

Accepted Solution

by:
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.
P.
0
 

Author Closing Comment

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

Format(Date(),"yyyyMMdd")

Thanks!1
0
 
LVL 5

Expert Comment

by:dvz-
ID: 24788312
You can also try

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

and

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.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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.
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 …

773 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