SQL only for defining a longdate format.

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
Who is Participating?

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.

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.

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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

danprhayesAuthor Commented:
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..

> 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.
Sorry, I've basically said the same thing as Pete, my apologies.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.