Is there a way to export Access date field without the time?

[magnum2us]

Right now to get my Access database into MySQl I have to copy it out of Access into Excel then I export it to a delimited text file.

I would like to eliminate the Excel part and go directly from Access to a text file.

The problem is with the date fields. When exporting from Access it always adds the time (00:00:00) which MySQL chokes on. It seems there should be a way to do this with the export function, so that the date is just the date and no time.

Any help would be appreciated.

Mark Agnum

 

[statsman]

Do a search on DatePart()

 

[parrothead517]

Need Date, but not Time in Exported Text File

I have a table that I need to export to a text file without the time attached to the date. I have Export Specs that I assign to the file when exporting it and when it shows me how it will look, there is no time. BUT low & behold, when I open the file, there's the 0:00:00. Is there an easy way to turn this off? I'm not very Access saavy.

 

[parrothead517]

Have you ever exported a query with the date formatted without the time:

MyNewDateFieldName:Format([MyDateField],"mm/dd/yyyy")

Thank you - I did read this, but as I said, I'm not very Access saavy. This statement alone reads like greek to me. Anything more specific you can provide would be great. I guess I'm baffled because I didn't have this issue in 2003, but do in 2007.

 

[parrothead517]

So I would put

Format([Effective_Date],"mm/dd/yyyy")

in the Criteria under that Field???

I tried that and still got the time. Is there an easier way to just turn it off? I have a new table every day that I already have to copy to reformat and this is another extra step. Boo on Access 2007... I'm thinking about going back to '03!!

 

[raskew]

Hi -

The DateValue() function will return just the portion of a date/time field or string. Example from the debug(immediate) window:

x = now()
? x 
4/7/2008 8:26:46 PM 

? datevalue(x)
4/7/2008 

to show this is, in fact, a date
? cdbl(datevalue(x))
 39545
 
? format(datevalue(x), "mm/dd/yyyy")
04/07/2008

Check-out the MSKB article describing how Access stores date/time:

http://support.microsoft.com/kb/q130514/

HTH - Bob

 

[mdgaw]

Both examples above seem to work well to display a date in MS Access...

MyNewDateFieldName:Format([MyDateField],"mm/dd/yyyy")

MyNewDateFieldName:datevalue([MyDateField])

However, if you use the Export Specifications, the date always ends up with 12:00:00 AM in the exported file. Is there anyway around this?

Thank you!

 

[mdgaw]

In order to make this work, I changed my table field for the date to a text field instead of a date/time field. (Before doing this, I tried to change the format of the field to just a date only...but I still ended up with time in the export.)

After I changed the field to text, the field was populated with a date --- 01/31/2017.

When I exported the field, it picked it up as a date only.

Hope this helps someone else as when you are using the Export Specifications I don't see any other way to do this.

Thanks!

 

[The_Doc_Man]

First, there is a danger in updating a thread that is nearly 10 years old.

Second, if you have a column in Excel and it is set to general date, you get midnight for date export because dates imply times. However, you can go back to set the format for the column to something that is date only, like ShortDate format.

 

[Gasman]

I have just exported a table of mine. The dates are just Date/Time, no extra format. When I export to excel I just get the date albeit in the format dd-mmm-yyyy

If I save the export spec and do it again, the same happens?

In order to make this work, I changed my table field for the date to a text field instead of a date/time field. (Before doing this, I tried to change the format of the field to just a date only...but I still ended up with time in the export.)

After I changed the field to text, the field was populated with a date --- 01/31/2017.

When I exported the field, it picked it up as a date only.

Hope this helps someone else as when you are using the Export Specifications I don't see any other way to do this.

Thanks!

 

[jimyorkwillfixit]

Since the field in the table is Date/Time, export the table as a query and format the date like this: format([DateFieldName],"MM/DD/YYYY"). Create an export specification and export the query to a text file. Unfortunately, export specifications do not show the data type. The first time, the date will have the time stamp. After exporting to text, import the file you just created and use the export specification for the import. At this point, you can modify the data type in the specification for the date field to Short Text. Save and overwrite the specification. Now export again using your export specification. This time the time stamp will be gone. Voila!