Change format of Date Value stored in database

[juvi]

hello,

I have got a question regarding date formatting. I have got a database where
a date is stored in the format (YYYY/MM/DD).

After reading the dates I need to be able to export these dates to a text
file in the format (DD.MM.YYYY). Is there a way to format this?

thx
juvi

 

[Daniel Pineault]

This is where the Format() function comes in. You can use it to specify how
it should display, in this case, your date

format([datefieldname],"dd.mm.yyyy")

So what I would suggest is you create a query in which you use the Format
function to get the date(s) as you please and then simply export the query
values.

You can then use the TransferText Method to export the query.
--
Hope this helps,

Daniel Pineault
For Access Tips and Examples: http://www.cardaconsultants.com/en/msaccess.php
If this post was helpful, please rate it by using the vote buttons.

 

[Rick Brandt]

hello,

I have got a question regarding date formatting. I have got a
database where a date is stored in the format (YYYY/MM/DD).

No, you only think that. DateTime values are not *stored* in Access with a
format. They are only displayed with one. The storage format is actually a
number that you wouldn't even recognize as a date if you were to look at it.

After reading the dates I need to be able to export these dates to a
text file in the format (DD.MM.YYYY). Is there a way to format this?

Daniel's response was spot on here. You create a query that formats your
date using the Format() function, NOT the Format property and then export
the query instead of the table.

 

[John Spencer]

The answer depends on what type of field you are using to store the date
- you have good answers if the date is stored in a dateTime field. If
the date STRING is being stored in a text field then you will need to
convert the string to a datetime field and then apply the display format.

IIF(IsDate([YourField]),Format(CDate([YourField]),"dd\.mm\.yyyy"),Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================