Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL only for defining a longdate format.

Posted on 2004-10-21
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 44

Expert Comment

ID: 12368685
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

ID: 12368816
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

ID: 12368830
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..


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 77

Accepted Solution

peter57r earned 75 total points
ID: 12369036

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

shanesuebsahakarn earned 75 total points
ID: 12369351
> 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

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

609 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