CODEWITHSUNDEEP
ms-accessms-access-2003
sikas
sikas

Reputation: 5523

Microsoft Access Date Format

I'm trying to retrieve a stored date in a MS Access 2003 database .. it is stored in the format dd/mm/yyyy, but when it is retrieved it comes in the format mm/dd/yyyy

How can I force the MS Access to return the date in the format dd/mm/yyyy??

Upvotes: 1

Views: 10755

Answers (6)

Frank Thynne
Frank Thynne

Reputation: 1

Be very careful when using today's date as a default value in Microsoft Access if the locale is not US. (Mine is UK).

  • Make sure that the date field type is specified as Date/Time.
  • Use Date() as the default value. Both rules must be followed. The conversion from a Date/Time field is correctly shown with the right format for the locale.

That overcame a problem I encountered when, in haste, I defined a field as short text and later introduced a default value =Date$(). Yes, it was sloppy, produced the US mm/dd/yyyy format and had to be repaired using a date picker.

Upvotes: 0

iDevlop
iDevlop

Reputation: 25252

You can use Excel TEXT function, which has the possibility to specify the locale. Here is a sample of a formatLocale MS Access UDF. 

Option Compare Database
Option Explicit

'see https://excel.tips.net/T003299_Specifying_a_Language_for_the_TEXT_Function.html
'for countries list

Dim xl As Excel.Application

Function formatLocale(dt As Variant, xlFormat As String) As Variant
    setXl
    formatLocale = xl.WorksheetFunction.Text(dt, xlFormat)
End Function

Sub test_formatLocale()
    'print this month's name in US english
    Debug.Print formatLocale(Now, "[$-409]mmmm")
End Sub

Function setXl()
    On Error Resume Next
    If xl Is Nothing Then
        'try getting
        Set xl = GetObject(, "Excel.Application")
    End If
    If xl Is Nothing Then
        Set xl = CreateObject("Excel.Application")
    End If
End Function

Upvotes: 1

Will Carr
Will Carr

Reputation: 1

In my experience. No matter what you do at some stage microsoft software will get confused about european dates be it asp, .net, sql, IE or the OS itself. Mainly when there is some ambiguity eg 6/5/2012 where it could be 6th May or 5th June. I guarantee at some stage this will go wrong.

Setting the locale will lull you into a false sense of security.

So either stick with the quirky USA date format mmddyyyy or do as we do and forget about microsoft's date format altogether and make you own based on yyyymmddhhmmss in a number field. It is more flexible and more powerful as it combines with time and allows very efficient time comparison. The problem with microsoft is that they try to fix things silently I don't find this helpful at all.

Upvotes: 0

Anton Kaiser
Anton Kaiser

Reputation: 723

Use the format function.

Format(dateVar, "dd/mm/yyyy")

where dateVar is preferably of type DateTime.

Upvotes: 0

sikas
sikas

Reputation: 5523

I managed to solve it.

Added format to the fields to force them to be dd/mm/yyyy

and in my C# application I tuned it to deal with the date in that format/ Everything is good

Upvotes: 1

NGLN
NGLN

Reputation: 43649

In Access, dates are internally always stored without date/month distinction. It is just a real number as in many databases and languages.

How that number is converted to and presented as date is entirely up to the client program and its local or system user settings.

In case of Access itself being that client program, dates are presented and edited in the current user setting format, presumable according to the current Windows user locale. Except in VBA: then the format for input and comparison in code as well as SQL is always mm/dd/yyyy as VBA is an English(US) written language.

Upvotes: 0

Related Questions