# Solved: Access - Convert Date Time to Date



## chudok01 (Sep 16, 2010)

I have a Date/Time field that I need converted to just a Time field.

It comes back with 2/11/12 0:00:00

I need just the 2/11/12

I thought about changing it to a text field but I think there will be an issue with the month because it does not bring back a 0 in from the months with only 1 digit.

This table that I am creating is being sent to a txt file and that is where I see the issue. It looks good in Access, but not the txt file.

Any suggestions?


----------



## Rockn (Jul 29, 2001)

Do you really need to convert it since the time is already a part of the field? You can separate out any part of a date/time field using functions just for display and querying purposes.


----------



## OBP (Mar 8, 2005)

As Rockn says in a query, Report or Form you can use the "Format" function to display the date in any way that you want.
You can also use functions in a query to separate out the various parts of the date like day, month, year time etc.


----------



## chudok01 (Sep 16, 2010)

The issue is that I am exporting this file in either a csv or txt for our ERP system and I either need it to be a text field that only looks like a date (not date and time) or a date field that only has a date.


----------



## OBP (Mar 8, 2005)

If you set the format to short date in the query and export the query what do you get?


----------



## chudok01 (Sep 16, 2010)

Still the long date. It views the short date but puts the long date.

This is what I have going on. I have a query that grabs data from another database and created table 1.
I then query table 1 and a couple of other tables and create another table 2.
Then I have query that created a table that exports. to either a csv or txt.

So in any of the steps I have tried to change the date and it is not working. I am going to try to change to a text with the mask of ##/##/## and see if that works.


----------



## OBP (Mar 8, 2005)

Are you using and Export Specification?
You could of course build the Date in a separate query column using
newdate: format([datefield], "dd") & "/" & format([datefield], "mm") & "/" format([datefield], "yyyy")
Where datefield is the actual name of your date field.


----------



## chudok01 (Sep 16, 2010)

Yes i am using the Export Specification. I remember I had issues with this prior and I can't remember how I fixed it.... I am going to have to research my past issue with the export specification. I can't even remember where to get to it...


----------



## chudok01 (Sep 16, 2010)

I ended up using

newdate: format([datefield], "dd") & "/" & format([datefield], "mm") & "/" format([datefield], "yyyy")

worked!!

Thank you!!!


----------

