in an Access query how can I ensure the dates in my table (dd/mm/yyyy 00:00:00) are extracted as yyyymmdd?

Question

Need the dates to come out as yyyymmdd for upload to another system, but what I've tried only resuilts in dd/mm/yyyy or does change the date to yyyymmdd but also leaves the time.

Answer 1

Create an expression field in your query with the expression DateString: Format([DATE],"yyyymmdd")

Where DateString is whatever you choose to name the field and [DATE] is the name of the field you need to express as a string.

It may also be possible to create an Input mask in the format you want but I failed to hack that! The above is by far the easiest and most logical method, anyway, since you want to represent the data already in a table, not change the way it is input.

Answer 2

You have to highlight the cell or cells you want to put the date into then click on format - cells - number - date - then select the format you want. If you can't select all cells at once you will have to do it individually. It will depend on where and how you are trying to move it to, if you are pasting it into another document try using paste speacial fuction.