converting from mm/dd/yyyy to dd/mm/yyyy

[Lifeseeker]

hi there,

I am doing this in query grid:

CDate(Format([test].[RefReferredOn],"dd/mm/yyyy"))

In table test, the refreferredon is already a date/time format (in mm/dd/yyyy)

When I run this query in Access, I still get the dates in mm/dd/yyyy format. What is the next step to get it in dd/mm/yyyy?

Thanks

 

[MarkK]

In a table a date/time field is stored as a number. If you don't assert a format on a date/time at retrieval time, Access will try and use your Windows date/time format. Note that by asserting a format on a date, the datatype becomes a string. Try this in the immediate pane . . .

? typename(format(date(), "mm/dd/yy"))

So, the VBA.Format() function returns a string. If you do this in a query . . .

CDate(Format([test].[RefReferredOn],"dd/mm/yyyy"))

. . . then your ReferredOn date/time from your table is converted to a string by Format, converted back to a date by CDate() and converted back to a string by the system to display the return value from CDate(). To show the formatted date, remove the conversion to CDate().
The simplest thing, if you prefer to use the dd/mm/yyy format, is change your date format to that in Windows. Then your query field can just be . . .

test.RefReferredOn

Failing that, just Format() the field . . .

Format(test.RefReferredOn, "dd/mm/yyyy")

. . . and leave it at that.

 

[CJ_London]

date is a decimal number, not mm/dd/yyyy or dd/mm/yyyy which is a format.

The value to the left of the decimal point represent the date and the value to the right represents the time as a percentage of 24hrs * 60mins * 60 seconds. This is why you can use the formula myDate+1 to get the following date. myDate+0.5 would add 12 hours

Access will default to the US format of mm/dd/yyyy and uses the UK/Euro format of dd/mm/yyyy when there is no contention - i.e. a date of 7/5/2015 will be interpreted as 5th July whereas a date of 31/5/2015 will be interpreted as 31st May.

formatted dates in sql need to be surrounded by the # character and in the format mm/dd/yyyy i.e. #7/5/2015#

the # characters are not required when making a comparison such as

where myDate1>myDate2

not sure why you are using this formula,

CDate(Format([test].[RefReferredOn],"dd/mm/yyyy"))

all you are doing is converting a date into a formatted string (using format) and then back to a date (using CDate)

 

[Brianwarnock]

Access will default to the US format of mm/dd/yyyy and uses the UK/Euro format of dd/mm/yyyy when there is no contention - i.e. a date of 7/5/2015 will be interpreted as 5th July whereas a date of 31/5/2015 will be interpreted as 31st May.

CJ the above statement appears to be missing a little but important point, namely that you are discussing the situation when the Windows setting is for the UK, and also it does not apply in the design grid.
Unless things have changed since I was using Access or I've forgotten more than I realise.

Brian