Converting text to date format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing a text file via TransferText into an Access Database. This
file contains a column that requires a date format. I am importing the file
into a temporary table, then appending the rows into the permanent table,
using CDate to convert the column containing the date information. I am
getting a data type conversion error. Can anyone tell me what I am doing
wrong?
 
CDate won't work with that. You need to put it in a format that CDate can
recognize as a date.

Do you have leading zeroes for January through September (i.e. 01, 02, ...
09 rather than 1, 2, ... 9)?

If so, try:

CDate(Format([TextDate], "00\/00\/00"))

If not, you'll need to write a function that'll be able to determine whether
it's a 1 or 2 digit month and a 1 or 2 digit day, and use the DateSerial
function.
 
January thru September does have leading zeroes. I tried the CDate example
you indicated and still received a datatype conversion error. I am applying
the CDate function to the text data in an update query, updating the table
containing the date-formatted column. Should I not use this update approach?

Douglas J. Steele said:
CDate won't work with that. You need to put it in a format that CDate can
recognize as a date.

Do you have leading zeroes for January through September (i.e. 01, 02, ...
09 rather than 1, 2, ... 9)?

If so, try:

CDate(Format([TextDate], "00\/00\/00"))

If not, you'll need to write a function that'll be able to determine whether
it's a 1 or 2 digit month and a 1 or 2 digit day, and use the DateSerial
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sheliahsawyer said:
mmddyyyy
Click to expand...
Click to expand...
 
What does the SQL of your query look like?

Do all of the records have a date for them, or are some of them Null?

What is your Short Date format set to through Regional Settings?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sheliahsawyer said:
January thru September does have leading zeroes. I tried the CDate example
you indicated and still received a datatype conversion error. I am applying
the CDate function to the text data in an update query, updating the table
containing the date-formatted column. Should I not use this update approach?

Douglas J. Steele said:
CDate won't work with that. You need to put it in a format that CDate can
recognize as a date.

Do you have leading zeroes for January through September (i.e. 01, 02, ....
09 rather than 1, 2, ... 9)?

If so, try:

CDate(Format([TextDate], "00\/00\/00"))

If not, you'll need to write a function that'll be able to determine whether
it's a 1 or 2 digit month and a 1 or 2 digit day, and use the DateSerial
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



mmddyyyy

:

What's the format of the date in the text field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I am importing a text file via TransferText into an Access
Click to expand...
Database.
This
file contains a column that requires a date format. I am
Click to expand...
importing
the
file
into a temporary table, then appending the rows into the permanent table,
using CDate to convert the column containing the date information.
Click to expand...
I
am
getting a data type conversion error. Can anyone tell me what I
Click to expand...
am
doing
wrong?
Click to expand...
Click to expand...
Click to expand...
 
No nulls....everything is populated.

The query is as follows:

INSERT INTO TABLE_NAME( DATE_DATE )
SELECT CDate(Format([TEXT_DATE],"00\/00\/00")) AS Expr1,
FROM TEXT_TABLE

The short date setting is mm/dd/yyyy

Douglas J. Steele said:
What does the SQL of your query look like?

Do all of the records have a date for them, or are some of them Null?

What is your Short Date format set to through Regional Settings?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sheliahsawyer said:
January thru September does have leading zeroes. I tried the CDate example
you indicated and still received a datatype conversion error. I am applying
the CDate function to the text data in an update query, updating the table
containing the date-formatted column. Should I not use this update approach?

Douglas J. Steele said:
CDate won't work with that. You need to put it in a format that CDate can
recognize as a date.

Do you have leading zeroes for January through September (i.e. 01, 02, ....
09 rather than 1, 2, ... 9)?

If so, try:

CDate(Format([TextDate], "00\/00\/00"))

If not, you'll need to write a function that'll be able to determine whether
it's a 1 or 2 digit month and a 1 or 2 digit day, and use the DateSerial
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



mmddyyyy

:

What's the format of the date in the text field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I am importing a text file via TransferText into an Access Database.
This
file contains a column that requires a date format. I am importing
the
file
into a temporary table, then appending the rows into the permanent
table,
using CDate to convert the column containing the date information. I
am
getting a data type conversion error. Can anyone tell me what I am
doing
wrong?
Click to expand...
Click to expand...
Click to expand...
 
All the date values are populated...no nulls.

The query appears as follows:

INSERT INTO TABLE_NAME( Load_Date)
SELECT CDate(Format([Test_Date],"00\/00\/00")) AS Expr1 FROM TABLE_TEXT

The short date format is mm/dd/yyyy.

Douglas J. Steele said:
What does the SQL of your query look like?

Do all of the records have a date for them, or are some of them Null?

What is your Short Date format set to through Regional Settings?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sheliahsawyer said:
January thru September does have leading zeroes. I tried the CDate example
you indicated and still received a datatype conversion error. I am applying
the CDate function to the text data in an update query, updating the table
containing the date-formatted column. Should I not use this update approach?

Douglas J. Steele said:
CDate won't work with that. You need to put it in a format that CDate can
recognize as a date.

Do you have leading zeroes for January through September (i.e. 01, 02, ....
09 rather than 1, 2, ... 9)?

If so, try:

CDate(Format([TextDate], "00\/00\/00"))

If not, you'll need to write a function that'll be able to determine whether
it's a 1 or 2 digit month and a 1 or 2 digit day, and use the DateSerial
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



mmddyyyy

:

What's the format of the date in the text field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I am importing a text file via TransferText into an Access Database.
This
file contains a column that requires a date format. I am importing
the
file
into a temporary table, then appending the rows into the permanent
table,
using CDate to convert the column containing the date information. I
am
getting a data type conversion error. Can anyone tell me what I am
doing
wrong?
Click to expand...
Click to expand...
Click to expand...
 
That looks as though it should work.

You might try

SELECT [TEXT_DATE]
FROM TEXT_TABLE
WHERE IsDate(Format([TEXT_DATE],"00\/00\/00")) = False

to try and identify which row's the problem.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sheliahsawyer said:
No nulls....everything is populated.

The query is as follows:

INSERT INTO TABLE_NAME( DATE_DATE )
SELECT CDate(Format([TEXT_DATE],"00\/00\/00")) AS Expr1,
FROM TEXT_TABLE

The short date setting is mm/dd/yyyy

Douglas J. Steele said:
What does the SQL of your query look like?

Do all of the records have a date for them, or are some of them Null?

What is your Short Date format set to through Regional Settings?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



January thru September does have leading zeroes. I tried the CDate example
you indicated and still received a datatype conversion error. I am applying
the CDate function to the text data in an update query, updating the table
containing the date-formatted column. Should I not use this update approach?

:

CDate won't work with that. You need to put it in a format that
Click to expand...
CDate
can
recognize as a date.

Do you have leading zeroes for January through September (i.e. 01,
Click to expand...
02,
....
09 rather than 1, 2, ... 9)?

If so, try:

CDate(Format([TextDate], "00\/00\/00"))

If not, you'll need to write a function that'll be able to determine whether
it's a 1 or 2 digit month and a 1 or 2 digit day, and use the DateSerial
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



mmddyyyy

:

What's the format of the date in the text field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I am importing a text file via TransferText into an Access Database.
This
file contains a column that requires a date format. I am importing
the
file
into a temporary table, then appending the rows into the permanent
table,
using CDate to convert the column containing the date
Click to expand...
information.
I
am
getting a data type conversion error. Can anyone tell me what
Click to expand...
I
am
doing
wrong?
Click to expand...
Click to expand...
Click to expand...
 
EVERY row returns false!!!

This is an example of the information contained in the column I am trying to
convert from text to date:

12122003.

I'm at a loss!

Douglas J. Steele said:
That looks as though it should work.

You might try

SELECT [TEXT_DATE]
FROM TEXT_TABLE
WHERE IsDate(Format([TEXT_DATE],"00\/00\/00")) = False

to try and identify which row's the problem.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sheliahsawyer said:
No nulls....everything is populated.

The query is as follows:

INSERT INTO TABLE_NAME( DATE_DATE )
SELECT CDate(Format([TEXT_DATE],"00\/00\/00")) AS Expr1,
FROM TEXT_TABLE

The short date setting is mm/dd/yyyy

Douglas J. Steele said:
What does the SQL of your query look like?

Do all of the records have a date for them, or are some of them Null?

What is your Short Date format set to through Regional Settings?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



January thru September does have leading zeroes. I tried the CDate
example
you indicated and still received a datatype conversion error. I am
applying
the CDate function to the text data in an update query, updating the table
containing the date-formatted column. Should I not use this update
approach?

:

CDate won't work with that. You need to put it in a format that CDate
can
recognize as a date.

Do you have leading zeroes for January through September (i.e. 01, 02,
....
09 rather than 1, 2, ... 9)?

If so, try:

CDate(Format([TextDate], "00\/00\/00"))

If not, you'll need to write a function that'll be able to determine
whether
it's a 1 or 2 digit month and a 1 or 2 digit day, and use the DateSerial
function.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
mmddyyyy

:

What's the format of the date in the text field?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



message
I am importing a text file via TransferText into an Access
Database.
This
file contains a column that requires a date format. I am
importing
the
file
into a temporary table, then appending the rows into the permanent
table,
using CDate to convert the column containing the date information.
I
am
getting a data type conversion error. Can anyone tell me what I
am
doing
wrong?
Click to expand...
Click to expand...
Click to expand...
 
sheliahsawyer said:
EVERY row returns false!!!

This is an example of the information contained in the column I am trying to
convert from text to date:

12122003.

I'm at a loss!
Click to expand...

You've got a four digit year so you need...

Format([TEXT_DATE],"00\/00\/0000")
instead of
Format([TEXT_DATE],"00\/00\/00")
 
Back
Top