Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Access SQL Date Format

Discussion in 'Information Technology' started by Goh, Jul 28, 2006.

  1. Goh

    Goh
    Expand Collapse
    Guest

    Hi,

    I got a big problem, inconsistent date format in access. I dun know why
    when I do query in access wich parameter dd-mm-yyyy when it goes to database
    it became mm-dd-yyyy.

    Okie let me explain more detail. I writing a vb6 program that will doing
    search to table in MS Access. the Query String in vb6 that i use is simple
    like this "select * from table1 where StartAt <=#5/8/06#" when u saw this I
    will assume is 5th of ogos 06. but MS access will interpret as 8th Mei 06.
    My Computer date format is dd-mm-yyyy.
    more over I already define the format for that StartAt column as
    dd-mm-yyyy why still cannot follow the rules.

    How can we actually define the date format in database.

    Please help me is a serious problem to me. The whole system work like crazy
    now.

    Thank in advance.
    With regards,
    Goh
     
  2. Loading...


  3. Ken Snell \(MVP\)

    Ken Snell \(MVP\)
    Expand Collapse
    Guest

    The Jet database engine (what runs the SQL statements) expects date values
    to be in an unambiguous format (e.g., yyyy-mmm-dd) or else it assumes that
    you're using the mm/dd/yyyy format. So I always cast date values with the
    Format function to ensure that the date is in the mm/dd/yyyy format.
    --

    Ken Snell
    <MS ACCESS MVP>



    "Goh" <goh@noemail.noemail> wrote in message
    news:O7Ph7RkoGHA.4184@TK2MSFTNGP04.phx.gbl...
    > Hi,
    >
    > I got a big problem, inconsistent date format in access. I dun know why
    > when I do query in access wich parameter dd-mm-yyyy when it goes to
    > database it became mm-dd-yyyy.
    >
    > Okie let me explain more detail. I writing a vb6 program that will
    > doing search to table in MS Access. the Query String in vb6 that i use is
    > simple like this "select * from table1 where StartAt <=#5/8/06#" when u
    > saw this I will assume is 5th of ogos 06. but MS access will interpret as
    > 8th Mei 06. My Computer date format is dd-mm-yyyy.
    > more over I already define the format for that StartAt column as
    > dd-mm-yyyy why still cannot follow the rules.
    >
    > How can we actually define the date format in database.
    >
    > Please help me is a serious problem to me. The whole system work like
    > crazy now.
    >
    > Thank in advance.
    > With regards,
    > Goh
    >
     
  4. Guest

    Guest
    Expand Collapse
    Guest

    SQL (an American invention) uses American date formats.

    SQL, used internationally, supports several date formats, but
    the standard is yyyy mm dd

    Windows and Access support and use other date formats
    for the Windows and Access user interfaces, which is nice.

    When you use the Windows and Access user interfaces,
    you should use the date formats you have specified for the
    Windows and Access user interfaces.

    And when you use SQL you need to use the SQL date formats.

    Either Month Day Year, or Year Month Day.

    If you use Year Month Day, you need to use four digits for the
    year to specify that you are using the standard date format rather
    than the original date format.

    (david)

    "Goh" <goh@noemail.noemail> wrote in message
    news:O7Ph7RkoGHA.4184@TK2MSFTNGP04.phx.gbl...
    > Hi,
    >
    > I got a big problem, inconsistent date format in access. I dun know

    why
    > when I do query in access wich parameter dd-mm-yyyy when it goes to

    database
    > it became mm-dd-yyyy.
    >
    > Okie let me explain more detail. I writing a vb6 program that will

    doing
    > search to table in MS Access. the Query String in vb6 that i use is

    simple
    > like this "select * from table1 where StartAt <=#5/8/06#" when u saw this

    I
    > will assume is 5th of ogos 06. but MS access will interpret as 8th Mei 06.
    > My Computer date format is dd-mm-yyyy.
    > more over I already define the format for that StartAt column as
    > dd-mm-yyyy why still cannot follow the rules.
    >
    > How can we actually define the date format in database.
    >
    > Please help me is a serious problem to me. The whole system work like

    crazy
    > now.
    >
    > Thank in advance.
    > With regards,
    > Goh
    >
    >
     
  5. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    ie
    SQLDate(ByVal V As Variant) As String
    If IsNull(V) Then
    SQLDate = "Null"
    Else
    SQLDate = "#" & Format(V,"mm\/dd\/yyyy") & "#"
    End If
    End Function

    HTH

    Pieter

    "Goh" <goh@noemail.noemail> wrote in message
    news:O7Ph7RkoGHA.4184@TK2MSFTNGP04.phx.gbl...
    > Hi,
    >
    > I got a big problem, inconsistent date format in access. I dun know why
    > when I do query in access wich parameter dd-mm-yyyy when it goes to
    > database it became mm-dd-yyyy.
    >
    > Okie let me explain more detail. I writing a vb6 program that will
    > doing search to table in MS Access. the Query String in vb6 that i use is
    > simple like this "select * from table1 where StartAt <=#5/8/06#" when u
    > saw this I will assume is 5th of ogos 06. but MS access will interpret as
    > 8th Mei 06. My Computer date format is dd-mm-yyyy.
    > more over I already define the format for that StartAt column as
    > dd-mm-yyyy why still cannot follow the rules.
    >
    > How can we actually define the date format in database.
    >
    > Please help me is a serious problem to me. The whole system work like
    > crazy now.
    >
    > Thank in advance.
    > With regards,
    > Goh
    >
     
  6. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    ie
    SQLDate(ByVal V As Variant) As String
    If IsNull(V) Then
    SQLDate = "Null"
    Else
    SQLDate = "#" & Format(V,"mm\/dd\/yyyy") & "#"
    End If
    End Function

    HTH

    Pieter

    "Goh" <goh@noemail.noemail> wrote in message
    news:O7Ph7RkoGHA.4184@TK2MSFTNGP04.phx.gbl...
    > Hi,
    >
    > I got a big problem, inconsistent date format in access. I dun know why
    > when I do query in access wich parameter dd-mm-yyyy when it goes to
    > database it became mm-dd-yyyy.
    >
    > Okie let me explain more detail. I writing a vb6 program that will
    > doing search to table in MS Access. the Query String in vb6 that i use is
    > simple like this "select * from table1 where StartAt <=#5/8/06#" when u
    > saw this I will assume is 5th of ogos 06. but MS access will interpret as
    > 8th Mei 06. My Computer date format is dd-mm-yyyy.
    > more over I already define the format for that StartAt column as
    > dd-mm-yyyy why still cannot follow the rules.
    >
    > How can we actually define the date format in database.
    >
    > Please help me is a serious problem to me. The whole system work like
    > crazy now.
    >
    > Thank in advance.
    > With regards,
    > Goh
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4182 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  7. Dragan Blagojevic

    Dragan Blagojevic
    Expand Collapse
    Guest

    I had lot of fun with the same, so few years back i made little func to
    alway format date to proper SQL format.
    Just use it wherever you need to pass date parameter to query

    Regards
    Dragan


    Function FormatToSQLDate(dDate As Variant, Optional bYearFirst As Boolean =
    False) As String
    Dim cMonth$
    Dim cDay$
    Dim cYear$

    On Error GoTo Err_Function

    If IsNull(dDate) Then

    If bYearFirst Then
    FormatToSQLDate = "0000/00/00"
    Else
    FormatToSQLDate = "00/00/0000"
    End If
    Exit Function
    End If
    If IsEmpty(dDate) Then
    If bYearFirst Then
    FormatToSQLDate = "0000/00/00"
    Else
    FormatToSQLDate = "00/00/0000"
    End If
    Exit Function
    End If
    If dDate = "" Then
    If bYearFirst Then
    FormatToSQLDate = "0000/00/00"
    Else
    FormatToSQLDate = "00/00/0000"
    End If
    Exit Function
    Else
    cMonth = Month(dDate)
    If CInt(cMonth) < 10 Then cMonth = "0" & cMonth
    cDay = Day(dDate)
    If CInt(cDay) < 10 Then cDay = "0" & cDay
    cYear = Year(dDate)
    If bYearFirst Then
    'FormatToSQLDate = Format$(dDate, "yyyy/mm/dd")
    FormatToSQLDate = cYear & "/" & cMonth & "/" & cDay
    Else
    'FormatToSQLDate = Format$(dDate, "mm/dd/yyyy")
    FormatToSQLDate = cMonth & "/" & cDay & "/" & cYear
    End If
    End If


    Exit_Function:

    Exit Function

    Err_Function:
    MsgBox Err.Description
    cMonth = "00"
    cDay = "00"
    cYear = "0000"
    Resume Exit_Function

    End Function




    <david@epsomdotcomdotau> wrote in message
    news:uK1ySnloGHA.3644@TK2MSFTNGP03.phx.gbl...
    > SQL (an American invention) uses American date formats.
    >
    > SQL, used internationally, supports several date formats, but
    > the standard is yyyy mm dd
    >
    > Windows and Access support and use other date formats
    > for the Windows and Access user interfaces, which is nice.
    >
    > When you use the Windows and Access user interfaces,
    > you should use the date formats you have specified for the
    > Windows and Access user interfaces.
    >
    > And when you use SQL you need to use the SQL date formats.
    >
    > Either Month Day Year, or Year Month Day.
    >
    > If you use Year Month Day, you need to use four digits for the
    > year to specify that you are using the standard date format rather
    > than the original date format.
    >
    > (david)
    >
    > "Goh" <goh@noemail.noemail> wrote in message
    > news:O7Ph7RkoGHA.4184@TK2MSFTNGP04.phx.gbl...
    >> Hi,
    >>
    >> I got a big problem, inconsistent date format in access. I dun know

    > why
    >> when I do query in access wich parameter dd-mm-yyyy when it goes to

    > database
    >> it became mm-dd-yyyy.
    >>
    >> Okie let me explain more detail. I writing a vb6 program that will

    > doing
    >> search to table in MS Access. the Query String in vb6 that i use is

    > simple
    >> like this "select * from table1 where StartAt <=#5/8/06#" when u saw this

    > I
    >> will assume is 5th of ogos 06. but MS access will interpret as 8th Mei
    >> 06.
    >> My Computer date format is dd-mm-yyyy.
    >> more over I already define the format for that StartAt column as
    >> dd-mm-yyyy why still cannot follow the rules.
    >>
    >> How can we actually define the date format in database.
    >>
    >> Please help me is a serious problem to me. The whole system work like

    > crazy
    >> now.
    >>
    >> Thank in advance.
    >> With regards,
    >> Goh
    >>
    >>

    >
    >
     

Share This Page