BCP and Date Problems

Hi,

We have a job that, every night, imports mail logs into SQL Server for processing. We receive these mail logs from the mail group, so we have no say in how the log should be formatted, basically we are given what we are given.

Our problem started occurring on Oct-01, and has occured every day since then.

The log file may contain rows with dates such as follows (don't ask me why there isn't a consistant format)

1) 2003-10-01 00:18:5
2) 2003-9-01 00:8:6
3) 2003-9-1 00:9:6
4) 2003-9-1 0:09:6

5) 2003-10-1 00:18:6
6) 2003-10-01 0:19:6
7) 2003-10-01 00:8:6

Now, 1-4 import. 5-7 don't.

From what I can see.

If the month is a single digit month, i.e. September above, then it will import basically all formats (e.g. 1 digit for the hour, 1 digit for the minute or 1 digit for the day).

If the month is a double digit month, i.e. October above, then it will not import unless the date is perfectly formatted (e.g. 2 digits for the hour AND 2 digits for the minute AND 2 digits for day).

An excerpt from the format file we are using is listed below.

Why does SQL Server allow 'bad' dates with single digit months, but not double digit months? It's really confusing me. I know the obvious solution, tell the Mail guys to properly format their files, but are there any other solutions?

Thanks heaps. Hope I've asked this clearly.

Format File
----
8.0
11
1 SQLCHAR 0 0 "" 0 TBL_MAIL_LOG_ID ""
2 SQLCHAR 0 0 "" 0 LOG_FILE_ID ""
3 SQLCHAR 0 1 " " 3 DATE ""

The last line reads (broken into individual lines below)

3
SQLCHAR
0
1
" " <-- there is a space in there
3
DATE
""Howdy

Sadly it appears its a quirk in SQL - unless a '1' is an '01' it seems to get its knickers in a knot, especially at the start of a column of information.

Try importing the raw data into another table that uses CHAR instead of datetime for the dates, then add the '0' to any dates required, then transfer across to final table. Its time consuming, but in the long run more robust.

Cheers

SG