If you want SQL Server to try and figure it out, just use CAST CAST('whatever' AS datetime) However that is a bad idea in general. There are issues with international dates that would come up. So as you've found, to avoid those issues, you want to use the ODBC canonical format of the date. That is format number 120, 20 is the format for just two digit years. I don't think SQL Server has a built-in function that allows you to provide a user given format. You can write your own and might even find one if you search online.

在MSSQL中隐式转换字符串到日期时间

create table tmp 
(
  ENTRYDATETIME datetime
);

insert into tmp (ENTRYDATETIME) values (getdate());
insert into tmp (ENTRYDATETIME) values ('20190101');  --convert string 'yyyymmdd' to datetime


select * from tmp where ENTRYDATETIME > '20190925'  --yyyymmdd 
select * from tmp where ENTRYDATETIME > '20190925 12:11:09.555'--yyyymmdd HH:MIN:SS:MS

对于这个问题，我使用的最好的解决方案是在Sql Server 2005中有一个使用DateTime的CLR函数。Parse或ParseExact函数返回指定格式的DateTime值。

通过您的查询处理器运行它。它格式化日期和/或时间，其中一个应该会给你你要找的东西。适应并不难:

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

我花了一分钟才弄明白，所以在这里它可能会帮助到别人:

在SQL Server 2012和更好的版本中，你可以使用这个函数:

SELECT DATEFROMPARTS(2013, 8, 19);

下面是我如何将日期的部分提取到这个函数中:

select
DATEFROMPARTS(right(cms.projectedInstallDate,4),left(cms.ProjectedInstallDate,2),right( left(cms.ProjectedInstallDate,5),2)) as 'dateFromParts'
from MyTable

用这个:

SELECT convert(datetime, '2018-10-25 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

转换代码请参考官方文档中的表格。