Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

You don't specify but I'll guess your talking about a query or some other SQL statement. Just tested with dBase sample file:

SELECT "dbase_f5".DATE
  FROM "dbase_f5"
  WHERE DATE between '1950-01-01' And '1970-01-01'

works without a problem. The # you mention is specific to the JET engine.

You don't specify but I'll guess your talking about a query or some other SQL statement. Just tested with dBase sample file:

SELECT "dbase_f5".DATE
  FROM "dbase_f5"
  WHERE DATE between '1950-01-01' And '1970-01-01'

works without a problem. The # you mention is specific to the JET engine.

Edit 5/3/2017:

By using the term dBase I was off on a wild goose chase. I was doing all my tests based upon you being connected directly to a dBase file. It was a waste of many hours since I could have easily answered this with the correct information. I have a converted file for you already.

The attached is your table with an added field at the end named "NewReadingDate". This is now an actual date field. I also included "Query2" which is a simple query to use the select between clause.

Updated table: Result.odb

Now after all that, If this answers your question please click on the ✔ (upper left area of my answer).

You don't specify but I'll guess your talking about a query or some other SQL statement. Just tested with dBase sample file:

SELECT "dbase_f5".DATE
  FROM "dbase_f5"
  WHERE DATE between '1950-01-01' And '1970-01-01'

works without a problem. The # you mention is specific to the JET engine.

Edit 5/3/2017:

By using the term dBase I was off on a wild goose chase. I was doing all my tests based upon you being connected directly to a dBase file. It was a waste of many hours since I could have easily answered this with the correct information. I have a converted file for you already.

The attached is your table with an added field at the end named "NewReadingDate". This is now an actual date field. I also included "Query2" which is a simple query to use the select between clause.

Updated table: Result.odb

Now after all that, If this answers your question please click on the ✔ (upper left area of my answer).

Edit:

I didn't include the "How" because it seems you do not have a grasp on SQL (please learn as this is essential in using ANY database). But here is what I did.

I did a 'quick & dirty' if you will, by creating a Temp table with your table selecting the record "ID and a re-format of the date in the text field:

SELECT "TestTable"."ID1", "B"."MYDATE" FROM "TestTable" INNER JOIN ( SELECT "ID1", SUBSTRING ( "ReadingDate", 7, 4 ) || '-' || SUBSTRING ( "ReadingDate", 4, 2 ) || '-' || SUBSTRING ( "ReadingDate", 1, 2 ) "MYDATE" FROM "TestTable" ) "B" ON "TestTable"."ID1" = "B"."ID1"

Next I add the new "Date" field to the existing table.

Finally, I updated the new field from the one generated in the temp table:

UPDATE "TestTable" SET "NewReadingDate" = (SELECT MYDATE FROM "TempTable" WHERE "TestTable"."ID1" = "TempTable"."ID1")

As for the checkmark:image description

Also, the ^ is an Upvote indicating approval of a question or answer & the down caret is dis-approval. The check mark signifies the correct answer was given.

You don't specify but I'll guess your talking about a query or some other SQL statement. Just tested with dBase sample file:

SELECT "dbase_f5".DATE
  FROM "dbase_f5"
  WHERE DATE between '1950-01-01' And '1970-01-01'

works without a problem. The # you mention is specific to the JET engine.

Edit 5/3/2017:

By using the term dBase I was off on a wild goose chase. I was doing all my tests based upon you being connected directly to a dBase file. It was a waste of many hours since I could have easily answered this with the correct information. I have a converted file for you already.

The attached is your table with an added field at the end named "NewReadingDate". This is now an actual date field. I also included "Query2" which is a simple query to use the select between clause.

Updated table: Result.odb

Now after all that, If this answers your question please click on the ✔ (upper left area of my answer).

Edit:

I didn't include the "How" because it seems you do not have a grasp on SQL (please learn as this is essential in using ANY database). But here is what I did.

I did a 'quick & dirty' if you will, by creating a Temp table with your table selecting the record "ID and a re-format of the date in the text field:

SELECT "TestTable"."ID1", "B"."MYDATE" FROM "TestTable" INNER JOIN ( SELECT "ID1", SUBSTRING ( "ReadingDate", 7, 4 ) SUBSTRING("ReadingDate",7,4) || '-' || SUBSTRING ( "ReadingDate", 4, 2 ) SUBSTRING("ReadingDate",4,2) || '-' || SUBSTRING ( "ReadingDate", 1, 2 ) "MYDATE" FROM "TestTable" ) "B" ON "TestTable"."ID1" = "B"."ID1"
SUBSTRING("ReadingDate",1,2) MYDATE from "TestTable"

Next I add the new "Date" field to the existing table.

Finally, I updated the new field from the one generated in the temp table:

UPDATE "TestTable" SET "NewReadingDate" = (SELECT MYDATE FROM "TempTable" WHERE "TestTable"."ID1" = "TempTable"."ID1")

As for the checkmark:image description

Also, the ^ is an Upvote indicating approval of a question or answer & the down caret is dis-approval. The check mark signifies the correct answer was given.

You don't specify but I'll guess your talking about a query or some other SQL statement. Just tested with dBase sample file:

SELECT "dbase_f5".DATE
  FROM "dbase_f5"
  WHERE DATE between '1950-01-01' And '1970-01-01'

works without a problem. The # you mention is specific to the JET engine.

Edit 5/3/2017:

By using the term dBase I was off on a wild goose chase. I was doing all my tests based upon you being connected directly to a dBase file. It was a waste of many hours since I could have easily answered this with the correct information. I have a converted file for you already.

The attached is your table with an added field at the end named "NewReadingDate". This is now an actual date field. I also included "Query2" which is a simple query to use the select between clause.

Updated table: Result.odb

Now after all that, If this answers your question please click on the ✔ (upper left area of my answer).

Edit:

I didn't include the "How" because it seems you do not have a grasp on SQL (please learn as this is essential in using ANY database). But here is what I did.

I did a 'quick & dirty' if you will, by creating a Temp table with your table selecting the record "ID and a re-format of the date in the text field:

SELECT "ID1", SUBSTRING("ReadingDate",7,4) || '-' || SUBSTRING("ReadingDate",4,2) || '-' || SUBSTRING("ReadingDate",1,2) MYDATE from "TestTable"

Next I add the new "Date" field to the existing table.

Finally, I updated the new field from the one generated in the temp table:

UPDATE "TestTable" SET "NewReadingDate" = (SELECT MYDATE FROM "TempTable" WHERE "TestTable"."ID1" = "TempTable"."ID1")

As for the checkmark:image description

Also, the ^ is an Upvote indicating approval of a question or answer & the down caret is dis-approval. The check mark signifies the correct answer was given.

Edit - UK format sample - ModifiedResult.odb