Date Conversion Not Working

I have a date coming in from a file in the format of:

8/5/2021 12:00:00 AM
5/18/2022 6:29:23 AM

I need to format these so I can insert them into SQL Server. I have tried everything I can think of with Date Format and Reformat Date functions.

All I get is an error triangle. I do not know how to figure out what is specifically wrong, although, I don't think anything is wrong from reading the docs and forums.

Any help is appreciated.

1 Please do not use reformat function load date field use Date format function instead
2 The date is 5/18/2022 6:29:23 AM and the format you are using is D/M/YYYY
There are only 12 months in the year and there is no 18th month

Format M/D/YYYY H:N:S TT works

Okay, you were correct about my formatting, but I have been at this for many hours and I think that was just a bad attempt.

However, I have corrected the issue you mentioned. My problem now is that when I run the conversion, I get an error that my target field expects a SmallDateTime, which I believe is the format YYYY-MM-DD HH:NN:SS Which is the data that is showing in the existing field for existing records.

So I went ahead an used the Reformat Date and I still get the same resulting error.

2 Errors from the execution log:
bcp_sendrow failed: 22008 [Microsoft][ODBC Driver 17 for SQL Server] Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

#@ Row 1, Column 16: Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding. @#

Below you will see the field definition:


Below you will see existing data in the field:


Below you will see how I am using the transformation:


Below you will see the results of the transformation using the above configuration:


Below you will see my configuration for the Reformat Date field.


Please note that I did not use the Date Format transformation as I was attempting to resolve the issue with the fractional seconds error. And I do know that I need a small date time in the specified format.

Thank you.

Hi Tim

For smalldatetime fields, you have to get rid of the seconds part

docs.microsoft.com/en-us/sql/t-sql/data-...iew=sql-server-ver15

This can be quickly done by adding another date format function



Please keep us posted on your progress