General Date/Time convert to Short date (1 Viewer)

[LEXCERM]

Hi all,

A csv file I am working with has the following example dates:

2/22/2012 12:00 AM using DateValue converts it to 22/02/2012 (dd/mm/yy) which is correct
3/7/2012 12:00 AM using DateValue converts it to 03/07/2012, but it should read 7/3/2012.

It seems like some sort of date conversion issue, but I can't get the dates to convert correctly.

Thanks in advance.

 

[pr2-eugin]

It depends on the Format you have on the system. In your table design view see how the Format of the Date appears as.
Is it ShortDate 19/06/2007, based on your format I think the short date format is defined by the regional settings as Short Date 06/19/2007.
You can use the Format method to change the format, works exactly like DateValue, but instead you set the Format.. The syntax like..

Format(Now,"dd/mm/yyyy") ' would return 08/08/2012
Format(Now,"hh:mm:ss") ' would return 17:48:15
Format(Now,"dd/mm hh:mm") ' would return 08/08 17:48

Flexibility of using Format is you can change the display as you desire..

 

[LEXCERM]

Hi Paul,

Thanks for the reply. Yes, I have tried the Format option as well, but still no joy. The original format of these dates is "mm/dd/yyyy hh:mm AMPM".

For example:
Format ([2/22/2012 12:00 AM],"dd/mm/yyyy") returns 22/02/2012
Format ([3/7/2012 12:00 AM], "dd/mm/yyyy") returns 03/07/2012

It seems that any date which has a single "d" figure i.e. 7, doesn't convert correctly. Maybe it's an underlying seriel number thing or something.

Weird.

Rgds.

 

[pr2-eugin]

Unfortunately, Access while importing, assumes that the entries are in right format..

I do not think there is a way around. In this scenario. But that is just from me, lets see if someone has another option.

 

[vbaInet]

When you export it to csv, export it as mmm/dd/yyyy or mmmm/dd/yyyy so 3/7/2012 will be Aug/07/2012. Then when you import it and format it, it will show the date correctly.