SQL only for defining a longdate format.

Posted on 2004-10-21
Last Modified: 2008-03-10
Dont even bother telling me that you can do this using the design feature of Access because I cannot open the db as its busy on the server.

I need to ALTER or if not drop and then CREATE another table with a date type set as longdate.

What is the SQL to do this.

CREATE Table blabla (TheDates longdate) will not work


CREATE Table blabla (TheDates date) will work but it doesnt allow you to specify which date type

Any ideas
Question by:danprhayes
    LVL 44

    Expert Comment

    The Foramt property of a Field in an Access table serves only to 'format' the display of the data when the table is viewed in DataSheet mode.  It has NOTHING whatsoever to do with how the data is actually stored in the table.  Date type data is actually stored, in Access, as a NUMBER of type Double ( a Decimal value, where the Integer part of the number - to the LEFT of the Decimal point - is the number of days since Dec 30, 1899, and the fractional part of the number - to the RIGHT of the decimal point - is the time, measured in Seconds since midnight, as a Fraction of 1 day (1 day = 24 hours = 86400 seconds).  How Dates are displayed is entirely up to you, in the user interface, and can be completely controlled by using the Format function (either in SQL), in the Format property of a TextBox control, or using the Format function in VBA.

    Why are you trying to change the Format when you Create the table in your code?  By the way, why is it necessary to create tables in code, in the first place?  There are a few - but mind you, a VERY FEW - valid reasons to create tables in code.

    LVL 77

    Expert Comment

    Hello danprhayes,

    The format property can't be changed through SQL because it is not a Jet property it is an Access UI property.
    So you have to use VBA to set the property.  This might involve creating the property if no value has been assigned previously.

    Here is some sample code:

    Sub ddd()
    Dim strFormat As String
    Dim db As Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prop As DAO.Property

    Set db = CurrentDb
    Set tdf = db.TableDefs("table7")  'your table here
    Set fld = tdf.Fields("datedef")  ' your field here
    strFormat = "Long Date"

    Set prop = fld.CreateProperty("Format", dbText, strFormat)
    On Error Resume Next
    fld.Properties.Append prop
    If Err.Number = 3367 Then  ' property already exists, so just set its value
        fld.Properties("Format") = strFormat
    End If

    Set db = Nothing
    Set tdf = Nothing
    Set fld = Nothing

    End Sub


    Author Comment

    Ok your date argument seems convincing but I disagree with the last paragraph.

    although if I am doing a select where the date 19/10/2004 is used in a variable within the SQL statement and it doesnt work because the db thinks I am looking for 10/19/2004 instead.  Longdate would not have this problem since 19 Oct 2004 cannot be interpreted as anything but.

    Why NOT create tables in code?  If you are using some sort of interface to do the creation for you its still using raw sql (ddl) behind it.

    Besides I cannot open up the db as you would normally be able to because its active on the server and I dont want to risk locking it or overwriting it when a user manipulating the db via asp pages is trying to access it..

    LVL 77

    Accepted Solution


    The format you use to display your date in tables or forms is not used for SQL purposes, so there is no value in changing the format for this reason.
    SQL date formats must be unambiguous (within a set of known formats).
    SQL will assume a date format of mm/dd/yy if the date it gets can be interpreted that way. If it can't, it looks at other formats to try to come up with a valid date.
    Your argument about presenting a date in clear month form is sound in principle but unfortunately the field format does not achieve this.  You must put the formatting explicitly into the sql string.
    I usually use Format(mydatefield, "yyyy-mm-dd") in my sql strings, as this is deemed to be unambiguous.

    LVL 41

    Assisted Solution

    > is used in a variable within the SQL statement and it doesnt work because the db thinks I am looking for 10/19/2004 instead.  Longdate would not have this problem since 19 Oct 2004 cannot be interpreted as anything but.

    No - the date format is *irrelevant* to SQL - whenever you execute SQL against a Jet database, you *must* pass the date value in US format or in any long format regardless of how the data is displayed in the field. So this will work:
    SELECT * FROM MyTable WHERE MyDate=#19 Oct 2004#
    or this:
    SELECT * FROM MyTable WHERE MyDate=#19/10/2004#
    or this:
    SELECT * FROM MyTable WHERE MyDate=#10/19/2004#

    since 19th Oct 2004 is an unambiguous date. The format of the date in the field has no bearing on the statement. The 11th Oct *is* an ambiguous date though. When searching for that date, you must either use something like:
    SELECT * FROM MyTable WHERE MyDate=#11 Oct 2004#
    SELECT * FROM MyTable WHERE MyDate=#10/11/2004#

    11/10/2004 will NOT work no matter what format the date is stored as - this will be interpreted by Jet as being 10th November 2004.
    LVL 41

    Expert Comment

    Sorry, I've basically said the same thing as Pete, my apologies.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Cisco Complete Network Certification Training

    If you’re an IT engineer or technician, it's time you take your career to the next level. This elite training bundle is brimming with all of the information you need to learn to sit for Cisco CNNA, CCNP, and CCENT certification exams.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now