I’m extremely new to bcp and I can’t seem to understand the issue that I’m facing.

Expected Format of Date is YYYYMMDD

I’ve been encountering this number of issues:

• Invalid date format
• Invalid character value for cast specification
• Invalid field size for datatype

Problem 1:
I have multiple date and time columns I need to pass in into SQL database. All of them are delimited by tab spaces. Some of the dates may be null (having spaces)

Attempted Solutions:
I’ve tried putting SQLDATE delimitor to be “t” but it gave me an error called ‘Invalid field size for datatype’ and I understand that we can’t use t for dates [according to CHATGPT]?

Problem 2:
Time is having 7 fractional seconds which I do not want and not sure how to get rid of it.

Attempted Solution:
As for time column, I’ve tried setting the fractional seconds to be 0 but the error tells me its detecting more fractional seconds then the ones declared.

Example:
Text Data file:
“ABC t t t20210119t002613tAt…”

Format File:
1 SQLCHAR 0 10 “t” 1 STRING_COLUMN “”
2 SQLDATE 0 8 “” 2 DTE_COLUMN_ONE SQLYYYYMMDD
3 SQLDATE 0 8 “” 3 DTE_COLUMN_TWO SQLYYYYMMDD
4 SQLTIME 0 6 “” 4 TIME_COLUMN_FOUR HHMMSS
5 SQLCHAR 0 1 “” 5 CHAR_COLUMN_FIVE “”
…

Expected output in DB:
STRING_COLUMN DTE_COLUMN_ONE DTE_COLUMN_TWO TIME_COLUMN_FOUR CHAR_COLUMN_FIVE
ABC NULL 2021-01-19 00:26:13 A

Real output in DB:
STRING_COLUMN DTE_COLUMN_ONE DTE_COLUMN_TWO TIME_COLUMN_FOUR CHAR_COLUMN_FIVE
ABC 5765-04-29 5765-04-06 03:49:57.7929760 0

My understanding of my problems:
Based on my understanding, it seems like the t delimitor is causing my data fields to not be assigned properly which resulted my char column to be something else instead.
But how can I import my date and time to be like my expected output?

I’ve tested my data with another similar table(same keys but all field are varchar(255)) to check if my allocation of the format file is correct, which it is.

Additional Question: Is it possible to directly import data from 1 table to another using bcp with specified data types?