这段代码解决了我的问题:

convert(date,YOUR_DATE,104)

如果你使用时间戳，你可以你下面的代码:

convert(datetime,YOUR_DATE,104)

对于这个问题，我使用的最好的解决方案是在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)'

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.

本页包含CONVERT函数可用的所有指定日期时间转换的一些引用。如果您的值不属于可接受的模式之一，那么我认为最好的方法是采用ParseExact路线。

SQL Server(2005, 2000, 7.0)没有任何灵活的，甚至是不灵活的方法来获取任意结构的字符串格式的datetime并将其转换为datetime数据类型。

所谓“随意”，我指的是“一种写作者(虽然可能不是你、我或地球另一边的人)认为是直观和完全明显的形式。”坦率地说，我不确定是否有这样的算法。