你可以使用这个函数 STR_TO_DATE() 使用正确的格式：

select str_to_date('03MAR2020:02:45:58.977000', '%d%b%Y:%k:%i:%s.%f')

请看演示。
结果：

2020-03-03 02:45:58.977000

在sql server中
2020年3月3日：02:45:58.977000无法转换tot datetime 因为，首先有一个额外的 : 年后签名，分数秒为6位，而不是最多3位。
如果字符串的长度和格式始终相同，则可以使用以下方法解决此问题：

DECLARE @example varchar(25) = '03MAR2020:02:45:58.977000'
SET @example = LEFT(@example, 22)   --- remove last 3 zeros
SET @example = STUFF(@example, CHARINDEX(':', @example), LEN(':'), ' ')  ---repalce the first ':'
SELECT convert(datetime, @example) --- convert to datetime

更多细节：以下是 datetime sql server中的数据类型。

YYYY is four digits from 1753 through 9999 that represent a year.

MM is two digits, ranging from 01 to 12, that represent a month in the specified year.

DD is two digits, ranging from 01 to 31 depending on the month, that represent a day of the specified month.

hh is two digits, ranging from 00 to 23, that represent the hour.

mm is two digits, ranging from 00 to 59, that represent the minute.

ss is two digits, ranging from 00 to 59, that represent the second.

n* is zero to three digits, ranging from 0 to 999, that represent the fractional seconds.

摘自sql server文档
https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15