SQL question: confused about dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

zladnaj - 25 Jan 2005 22:16 GMT

I'm creating an Access db for a student project, but I've hit a snag. I
have to use SQL insert statements.

I have defined the 2nd and 3rd fields in a table as a DATE data type.

Here is my statement:
INSERT INTO TRIP
VALUES (263,2005-01-18,2005-01-19,'Phoenix','AZ','4023892991',3);

But when I then go to the table, Access renders it like this:

263 | 6/8/1905 | 6/7/1905 | Phoenix | AZ | 4023892.......

Does anyone know how I express my date values in my SQL statement so
that they look like US-English dates, i.e. (1/18/05)?

Or, is the problem with the data definition - that I've defined these
fields as the DATE data type when they should be something else?

Signature

zladnaj

Reply to this Message

Bill Edwards - 26 Jan 2005 05:32 GMT

see:

http://www.mvps.org/access/datetime/date0005.htm

> I'm creating an Access db for a student project, but I've hit a snag. I
> have to use SQL insert statements.
[quoted text clipped - 14 lines]
> Or, is the problem with the data definition - that I've defined these
> fields as the DATE data type when they should be something else?

Reply to this Message

Chris2 - 26 Jan 2005 06:37 GMT

> I'm creating an Access db for a student project, but I've hit a snag. I
> have to use SQL insert statements.
[quoted text clipped - 4 lines]
> INSERT INTO TRIP
> VALUES (263,2005-01-18,2005-01-19,'Phoenix','AZ','4023892991',3);

Try:
INSERT INTO TRIP
VALUES (263,"2005-01-18","2005-01-19",'Phoenix','AZ','4023892991',3);

> But when I then go to the table, Access renders it like this:
>
> 263 | 6/8/1905 | 6/7/1905 | Phoenix | AZ | 4023892.......

Go to Tools>Macro>Visual Basic Editor.

Go to View>Immediate Window

In the Immediate Window, attempt two experiments. Type, exactly as
follows, and hit enter:

? CDate(2005-01-18)

And you get: 06/08/1905

Type, exactly, and hit enter:

? CDate("2005-01-18")

And you get: 01/18/2005

> Does anyone know how I express my date values in my SQL statement so
> that they look like US-English dates, i.e. (1/18/05)?
> zladnaj

Reply to this Message

Van T. Dinh - 26 Jan 2005 13:31 GMT

...
VALUES (263,#01/18/2005#,#01/19/2005#,'Phoenix','AZ','4023892991',3)

Explicit date values must be in US format mm/dd/yyyy and enclosed in hashes.

Signature

HTH
Van T. Dinh
MVP (Access)

Reply to this Message