SQL (Access) Database - Date field?

[Killswitch]

Hi,

I'm using this to create a table in a databse:

Create table Accounts(ID autoincrement,FirstName text(100), Surname text(100), Form text(3), Rollnumber long, Message1 text(225), Message2 text(225), Message3 text(225), Accepted text(225) ,constraint Accounts unique(id));

However I can't seem to create a date field. How can I do this?

[eriansa]

Having the same problem with BLOB and Memo...

[Fangbeast]

I was looking through my MySQL manual and found this... (Working at the MySQL prompt at the time) and I tested the creation as you see below. Does this help? I believe MsAccess date field creation is the same..


mysql> CREATE TABLE y2k (date DATE,
-> date_time DATETIME,
-> time_stamp TIMESTAMP);

Query OK, 0 rows affected (0.01 sec)


mysql> INSERT INTO y2k VALUES
-> ('1998-12-31','1998-12-31 23:59:59',19981231235959),
-> ('1999-01-01','1999-01-01 00:00:00',19990101000000),
-> ('1999-09-09','1999-09-09 23:59:59',19990909235959),
-> ('2000-01-01','2000-01-01 00:00:00',20000101000000),
-> ('2000-02-28','2000-02-28 00:00:00',20000228000000),
-> ('2000-02-29','2000-02-29 00:00:00',20000229000000),
-> ('2000-03-01','2000-03-01 00:00:00',20000301000000),
-> ('2000-12-31','2000-12-31 23:59:59',20001231235959),
-> ('2001-01-01','2001-01-01 00:00:00',20010101000000),
-> ('2004-12-31','2004-12-31 23:59:59',20041231235959),
-> ('2005-01-01','2005-01-01 00:00:00',20050101000000),
-> ('2030-01-01','2030-01-01 00:00:00',20300101000000),
-> ('2040-01-01','2040-01-01 00:00:00',20400101000000),
-> ('9999-12-31','9999-12-31 23:59:59',99991231235959);

Query OK, 14 rows affected (0.01 sec)

Records: 14 Duplicates: 0 Warnings: 2

mysql> SELECT * FROM y2k;

+------------+---------------------+----------------+
| date | date_time | time_stamp |
+------------+---------------------+----------------+
| 1998-12-31 | 1998-12-31 23:59:59 | 19981231235959 |
| 1999-01-01 | 1999-01-01 00:00:00 | 19990101000000 |
| 1999-09-09 | 1999-09-09 23:59:59 | 19990909235959 |
| 2000-01-01 | 2000-01-01 00:00:00 | 20000101000000 |
| 2000-02-28 | 2000-02-28 00:00:00 | 20000228000000 |
| 2000-02-29 | 2000-02-29 00:00:00 | 20000229000000 |
| 2000-03-01 | 2000-03-01 00:00:00 | 20000301000000 |
| 2000-12-31 | 2000-12-31 23:59:59 | 20001231235959 |
| 2001-01-01 | 2001-01-01 00:00:00 | 20010101000000 |
| 2004-12-31 | 2004-12-31 23:59:59 | 20041231235959 |
| 2005-01-01 | 2005-01-01 00:00:00 | 20050101000000 |
| 2030-01-01 | 2030-01-01 00:00:00 | 20300101000000 |
| 2040-01-01 | 2040-01-01 00:00:00 | 00000000000000 |
| 9999-12-31 | 9999-12-31 23:59:59 | 00000000000000 |
+------------+---------------------+----------------+

14 rows in set (0.00 sec)

[the.weavster]

There does seem to be differences in the way date fields are handled from ODBC driver to ODBC driver.

I have started using VARCHAR(10) and formating the date like 'yyyy/mm/dd'.

Doing this you can still use greater than, less than and between statements.

[bhatkins2000]

I have a problem related to this issue.

I can get a table created with a DateTime type column (in Access). However, when I try to populate the table with a number (date serial) from a .csv file, the query bombs.

SO the question is How do I get the number from PB to Access in a date format that Access will accept?

Any ideas?

[Straker]

The ODBC driver for Access has always been extremely flawed and problematic. If you can use a different DB - do it! Try SqlLite or Cheetah if you need a small footprint.

@bhatkins2000: Welcome to the forums. Try this format for your datetime column in a CSV, and see if it works:

"YYYY-MM-DD HH:MM:SS" so

"2006-05-17 17:45:00"

Try it with double quotes, single quotes, and no quotes.

[Dare2]

I always use a string "YYYYMMDDHHmmSS" for dates and do my own conversions.

Also Access/Jet has different versions and these (IIRC) handle it in different ways (also the LIKE syntax wildcards, etc) so maybe, as Straker says, go with a different DB.

Aside: But not Cheetah if you're into SQL. (Actually, Cheetah uses Dbase-ish data structures. Internally it is a good engine but the interface is is a bit cumbersome, so go with something else IMO)