?
Solved

SQL only for defining a longdate format.

Posted on 2004-10-21
6
Medium Priority
?
617 Views
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

but

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

Any ideas
0
Comment
Question by:danprhayes
6 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
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.

AW
0
 
LVL 77

Expert Comment

by:peter57r
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



Pete
0
 

Author Comment

by:danprhayes
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..

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 77

Accepted Solution

by:
peter57r earned 75 total points
ID: 12369036
danprhayes,

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.


Pete
0
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
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#
OR:
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.
0
 
LVL 41

Expert Comment

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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Audit trails are very important in any system to hold people responsible for certain transactions and hold them to take ownership of their actions. This article is dedicated to all novice "Microsoft Access" developers.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

601 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