Format Date yyyymmdd to mm/dd/yyyy

[rxm01l]

The Date Field from UNIX after I extract, it looked like this 20130305. I would like to change it or Format it to look like this 03/05/2013. Is there an easy way for me to format this date field in Access? I'd like to do this on a Form.. Thanks in advace..

 

[mdlueck]

Here you go...

Convert date format from YYYY-MM-DD to DD/MM/YYYY
http://www.access-programmers.co.uk/forums/showthread.php?t=230590#post1176680

 

[bob fitz]

MyDate: Format(Left([UnixDate],4) & "/" & Mid([Unixdate],5,2) & "/" & Right([unixdate],2),"mm/dd/yyyy")

I think you could use something like this in forms Record Source query.

 

[spikepl]

Both of the above suggestions work OK, but neither is strictly correct. The tolerance of Access with regard to data types never ceases to amaze me.

 

[bob fitz]

spikepl

Can you please explain why the suggestion I made is not "strictly correct"?
What would be the correct way?

 

[spikepl]

Your solution relies on an implicit conversion of a string to a date, namely from "yyyy/mm/dd" to a date variable, which then, in turn, is formatted by the Format-function according to the format specification supplied in the call.. The date inside the string is unambiguous, and Access accepts this, so everything goes well. If the more common ambiguous format for date was used in the string, we would be back to the usual US/non-US date problems stemming from an implicit conversion (when regional settings are not US).

 

[bob fitz]

Your solution relies on an implicit conversion of a string to a date, namely from "yyyy/mm/dd" to a date variable, which then, in turn, is formatted by the Format-function according to the format specification supplied in the call.. The date inside the string is unambiguous, and Access accepts this, so everything goes well. If the more common ambiguous format for date was used in the string, we would be back to the usual US/non-US date problems stemming from an implicit conversion (when regional settings are not US).

Thank you for the explanation.
Can you give us a "correct" solution.

 

[Pat Hartman]

Format() converts a date to a string which is exactly what you don't want when working with dates since the date will now be treated as a string and will not sort correctly nor will it compare correctly.
"05/05/2009" will not sort before "01/01/2013" nor will it be less than if you compare the two strings. However, if the two values are real dates, the May date will be less than the January date and will sort ahead of it. See below for how dates are stored internally.

The best solution is to convert the Unix string to a column defined as date/time so you can work with it using date functions and so it will sort and compare correctly.

Formatting is ONLY for display to humans. Internally dates are stored as double precision numbers with the integer representing the number of days since Dec 30, 1899 and the decimal being the time since midnight.

print now()
03/05/13 11:37:54 PM
print cdbl(#03/05/13 11:37:54 PM#)
41338.9846527778

To convert the Unix string to a date:
CDate(Left(UnixDate, 4) & "/" & Mid(UnixDate, 3,2) & "/" & Right(UnixDate, 2))

I used the year/month/day order because it is unambiguous and will not be impacted by your Windows date setting.

 

[Brianwarnock]

And I always used DateSerial as I wanted the result to be according to my regional settings.
Please critic.

Brian

Edit actually that is not quite true, if creating a date when US format required then I would use CDate

 

[rxm01l]

Thank You everyone so much For taking the time to answer my post. I will try to use all the recomendations that is suggested to me and determine which work best for my user..Thank You..

 

[Pat Hartman]

And I always used DateSerial as I wanted the result to be according to my regional settings.
Please critic.

If the result is a date/time datatype, it has no regional format. As I said earlier, all dates are stored internally as double precision numbers. It is only when they are displayed for human consumption that date format becomes relevant. To avoid confusion, NEVER format a date UNLESS you are displaying it on a form or report. So in queries and code you work with date/time data types and never need to worry about format because they are double precision numbers rather than formatted strings. When using unbound controls to enter a date, make sure to give it a date format so Access "knows" it is a date.