blocks|key|1715121|text|对于这个问题，我使用的最佳解决方案是在Sql+Server2005中使用一个CLR函数，该函数使用DateTime.Parse或ParseExact函数之一以指定的格式返回DateTime值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715122|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime.Parse or ParseExact function to return the DateTime value with a specified format.

blocks|key|1500106|text|在您的查询处理器中运行此命令。它像这样格式化日期和/或时间，其中之一应该会给你你正在寻找的东西。适应起来并不难：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1500107|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)'|code-block|syntax|javascript|1500108|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Run this through your query processor. It formats dates and/or times like so and one of these should give you what you're looking for. It wont be hard to adapt: 

<pre><code>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)'
</code></pre>

blocks|key|1500176|text|如果你想让SQL+Server试着解决这个问题，只需使用CAST+(“随便”作为datetime)，但这通常不是一个好主意。国际日期会出现一些问题。因此，正如您已经发现的，为了避免这些问题，您希望使用ODBC规范格式的日期。这就是格式编号120，20是只有两位数年份的格式。我不认为SQL+Server有一个内置的函数，可以让您向用户提供给定的格式。你可以自己写，如果你在网上搜索，甚至可能会找到一个。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1500177|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|1500252|text|此page对CONVERT函数可用的所有指定日期时间转换都有一些引用。如果您的值不属于可接受的模式之一，那么我认为最好的方法是采用ParseExact路线。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1500253|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx^0|1|4|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

This <a href="http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx" rel="nofollow noreferrer">page</a> has some references for all of the specified datetime conversions available to the CONVERT function. If your values don't fall into one of the acceptable patterns, then I think the best thing is to go the ParseExact route.

blocks|key|1500317|text|SQL+Server+(2005、2000、7.0)没有任何灵活或甚至不灵活的方法来获取任意结构的字符串格式的日期时间，并将其转换为日期时间数据类型。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1500318|我所说的“任意的”，是指“一种形式，写它的人，虽然可能不是你或我或地球另一端的人，会认为是直观的和完全显而易见的。”坦率地说，我不确定是否有这样的算法。|1500319|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

SQL Server (2005, 2000, 7.0) does not have any flexible, or even non-flexible, way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type.

By "arbitrarily", I mean "a form that the person who wrote it, though perhaps not you or I or someone on the other side of the planet, would consider to be intuitive and completely obvious." Frankly, I'm not sure there is any such algorithm.

blocks|key|1715367|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715368|Cast('7/7/2011'+as+datetime)|code-block|syntax|javascript|1715369|和|1715370|Convert(varchar(30),'7/7/2011',102)|1715371|有关更多详细信息，请参阅CAST+and+CONVERT+(Transact-SQL)。|offset|length|1715372|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx^0|0|0|0|0|C|V|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

Try this

<pre><code>Cast('7/7/2011' as datetime)
</code></pre>

and

<pre><code>Convert(varchar(30),'7/7/2011',102)
</code></pre>

See <a href="https://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx" rel="noreferrer">CAST and CONVERT (Transact-SQL)</a> for more details.

blocks|key|1500408|text|就我个人而言，如果您正在处理任意的或完全不符合规则的格式，只要提前知道它们是什么或将要是什么，那么只需使用regexp提取您想要的日期部分，并形成一个有效的日期/日期时间组件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1500409|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Personally if your dealing with arbitrary or totally off the wall formats, provided you know what they are ahead of time or are going to be then simply use regexp to pull the sections of the date you want and form a valid date/datetime component.

blocks|key|1500430|text|使用以下命令：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1500431|SELECT+convert(datetime,+'2018-10-25+20:44:11.500',+121)+--+yyyy-mm-dd+hh:mm:ss.mmm|code-block|syntax|javascript|1500432|有关转换代码，请参阅official+documentation中的表格。|offset|length|1500433|entityMap|0|LINK|mutability|MUTABLE|url|https://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx^0|0|0|A|M|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

Use this:

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

And refer to the table in the <a href="https://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx" rel="noreferrer">official documentation</a> for the conversion codes.

blocks|key|1715544|text|我花了一分钟才弄明白这一点，所以这里是它可能对某人有帮助的情况：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715545|在SQL+Server+2012及更高版本中，您可以使用此函数：|1715546|SELECT+DATEFROMPARTS(2013,+8,+19);|code-block|syntax|javascript|1715547|下面是我如何最终提取要放入此函数的日期部分的方法：|1715548|select
DATEFROMPARTS(right(cms.projectedInstallDate,4),left(cms.ProjectedInstallDate,2),right(+left(cms.ProjectedInstallDate,5),2))+as+'dateFromParts'
from+MyTable|1715549|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Took me a minute to figure this out so here it is in case it might help someone:

In SQL Server 2012 and better you can use this function:

<pre><code>SELECT DATEFROMPARTS(2013, 8, 19);
</code></pre>

Here's how I ended up extracting the parts of the date to put into this function:

<pre><code>select
DATEFROMPARTS(right(cms.projectedInstallDate,4),left(cms.ProjectedInstallDate,2),right( left(cms.ProjectedInstallDate,5),2)) as 'dateFromParts'
from MyTable
</code></pre>

blocks|key|1715568|text|dateadd(day,0,'10/15/2008+10:06:32+PM')|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1715569|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>dateadd(day,0,'10/15/2008 10:06:32 PM')
</code></pre>

blocks|key|1715600|text|在MSSQL中将字符串隐式转换为日期时间|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715601|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|code-block|syntax|javascript|1715602|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

convert string to datetime in MSSQL implicitly

<pre><code>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 &gt; '20190925' --yyyymmdd 
select * from tmp where ENTRYDATETIME &gt; '20190925 12:11:09.555'--yyyymmdd HH:MIN:SS:MS



</code></pre>

blocks|key|1715694|text|我知道这是一篇有很多答案的老帖子，但很多人认为他们要么需要把东西拆开，然后把它们放在一起，要么他们坚持认为没有办法隐含地完成OP最初要求的转换。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715695|为了复习并希望为其他有同样问题的人提供一个简单的答案，OP询问了如何将'10/15/2008+10:06:32+PM‘转换为日期时间。现在，SQL+Server确实对时间转换有一些语言依赖性，但如果语言是英语或类似的语言，这就成了一个简单的问题……只要做转换就行了，不用担心格式。例如(您可以使用CONVERT或CAST)...|1715696|+SELECT+UsingCONVERT+=+CONVERT(DATETIME,'10/15/2008+10:06:32+PM')
++++++++,UsingCAST+++=+CAST('10/15/2008+10:06:32+PM'+AS+DATETIME)
;|code-block|syntax|javascript|1715697|..。这就产生了下面的答案，这两个答案都是正确的。|1715698|​|1715699|📷|atomic|offset|length|1715700|1715701|就像他们在电视广告上说的那样，“等等！先别点菜！不需要额外的费用，它可以做更多的事情！”|1715702|让我们看看使用DATETIME进行时间转换的真正威力，并部分检查DATETIME2错误。看看DATETIME可以自动魔术处理而DATETIME2不能处理的奇怪格式。运行以下代码并查看...|1715703|--=====+Set+the+language+for+this+example.
++++SET+LANGUAGE+ENGLISH+--Same+a+US-English
;
--=====+Use+a+table+constructor+as+if+it+were+a+table+for+this+example.
+SELECT+*
++++++++,DateTimeCONVERT++=+TRY_CONVERT(DATETIME,StringDT)
++++++++,DateTimeCAST+++++=+TRY_CAST(StringDT+AS+DATETIME)
++++++++,DateTime2CONVERT+=+TRY_CONVERT(DATETIME2,StringDT)
++++++++,DateTime2CAST++++=+TRY_CAST(StringDT+AS+DATETIME2)
+++FROM+(
+++++++++VALUES
+++++++++('Same+Format+As+In+The+OP'++++,'12/16/2001+01:51:01+PM')
++++++++,('Almost+Normal'+++++++++++++++,'16+December,+2001+1:51:01+PM')
++++++++,('More+Normal'+++++++++++++++++,'December+16,+2001+01:51:01+PM')
++++++++,('Time+Up+Front+%2B+Spaces'++++++,'+++13:51:01++16+December+++2001')
++++++++,('Totally+Whacky+Format+#01'+++,'++16++13:51:01++December+++2001')
++++++++,('Totally+Whacky+Format+#02'+++,'++16++++December+13:51:01++2001++')
++++++++,('Totally+Whacky+Format+#03'+++,'++16++++December+01:51:01++PM+2001++')
++++++++,('Totally+Whacky+Format+#04'+++,'+2001+16++++December+01:51:01++PM+')
++++++++,('Totally+Whacky+Format+#05'+++,'+2001++++December+01:51:01++PM++16++')
++++++++,('Totally+Whacky+Format+#06'+++,'+2001+16++++December++01:51:01+PM++')
++++++++,('Totally+Whacky+Format+#07'+++,'+2001+16++++December++13:51:01+PM++')
++++++++,('Totally+Whacky+Format+#08'+++,'+2001+16++13:51:01+PM++December++++')
++++++++,('Totally+Whacky+Format+#09'+++,'+++13:51:01+++PM++2001.12/16+')
++++++++,('Totally+Whacky+Format+#10'+++,'+++13:51:01+++PM++2001.December/16+')
++++++++,('Totally+Whacky+Format+#11'+++,'+++13:51:01+++PM++2001.Dec/16+')
++++++++,('Totally+Whacky+Format+#12'+++,'+++13:51:01+++PM++2001.Dec.16+')
++++++++,('Totally+Whacky+Format+#13'+++,'+++13:51:01+++PM++2001/Dec.16')
++++++++,('Totally+Whacky+Format+#14'+++,'+++13:51:01+++PM++2001+.+12/16+')
++++++++,('Totally+Whacky+Format+#15'+++,'+++13:51:01+++PM++2001+.+December+/+16+')
++++++++,('Totally+Whacky+Format+#16'+++,'+++13:51:01+++PM++2001+.+Dec+/+++16+')
++++++++,('Totally+Whacky+Format+#17'+++,'+++13:51:01+++PM++2001+.+Dec+.+++16+')
++++++++,('Totally+Whacky+Format+#18'+++,'+++13:51:01+++PM++2001+/+Dec+.+++16')
++++++++,('Totally+Whacky+Format+#19'+++,'+++13:51:01+++PM++2001+.+Dec+-+++16+')
++++++++,('Totally+Whacky+Format+#20'+++,'+++13:51:01+++PM++2001+-+Dec+-+++16+')
++++++++,('Totally+Whacky+Format+#21'+++,'+++13:51:01+++PM++2001+-+Dec+.+++16')
++++++++,('Totally+Whacky+Format+#22'+++,'+++13:51:01+++PM++2001+-+Dec+/+++16+')
++++++++,('Totally+Whacky+Format+#23'+++,'+++13:51:01+++PM++2001+/+Dec+-+++16')
++++++++,('Just+the+year'+++++++++++++++,'+2001++++++')
++++++++,('YYYYMM'++++++++++++++++++++++,'+200112++++++')
++++++++,('YYYY+MMM'++++++++++++++++++++,'2001+Dec')
++++++++,('YYYY-MMM'++++++++++++++++++++,'2001-Dec')
++++++++,('YYYY++++.+++++MMM'+++++++++++,'2001++++.+++++Dec')
++++++++,('YYYY++++/+++++MMM'+++++++++++,'2001++++/+++++Dec')
++++++++,('YYYY++++-+++++MMM'+++++++++++,'2001++++/+++++Dec')
++++++++,('Forgot+The+Spaces+#1'++++++++,'2001December26')
++++++++,('Forgot+The+Spaces+#2'++++++++,'2001Dec26')
++++++++,('Forgot+The+Spaces+#3'++++++++,'26December2001')
++++++++,('Forgot+The+Spaces+#4'++++++++,'26Dec2001')
++++++++,('Forgot+The+Spaces+#5'++++++++,'26Dec2001+13:51:01')
++++++++,('Forgot+The+Spaces+#6'++++++++,'26Dec2001+13:51:01PM')
++++++++,('Oddly,+this+doesn''t+work'+++,'2001-12')
++++++++,('Oddly,+this+doesn''t+work'+++,'12-2001')
++++++++)+v+(Description,StringDT)
;|1715704|所以是的..。SQL+Server实际上有一种非常灵活的方法来处理各种奇怪的时态格式，并且不需要特殊的处理。我们甚至不需要删除添加到24小时时间中的“PM”。这是"PFM“(纯粹的魔法)。|1715705|情况会有所不同，这取决于您为服务器选择的语言，但大部分都将以任何一种方式进行处理。|1715706|这些“自动魔术”转换并不是什么新鲜事。它们可以追溯到很久以前。|1715707|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/111d6d222ddbb67f01859ed4184c3489.png|imageAlt^0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1E|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1F|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1G|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|1H|8|@]|9|@]|A|$]]|$1|M|3|N|5|O|7|1I|8|@]|9|@$P|1J|Q|1K|1|1L]]|A|$]]|$1|R|3|L|5|6|7|1M|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1N|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|1O|8|@]|9|@]|A|$]]|$1|W|3|X|5|F|7|1P|8|@]|9|@]|A|$G|H]]|$1|Y|3|Z|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|10|3|11|5|6|7|1R|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1S|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|1T|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B|1C|-4]]]]

I know this is a wicked old post with a whole lot of answers but a lot of people think that they NEED to either break things apart and put them back together or they insist that there's no way to implicitly do the conversion the OP original asked for. 

To review and to hopefully provide an easy answer to others with the same question, the OP asked how to convert '10/15/2008 10:06:32 PM' to a DATETIME. Now, SQL Server does have some language dependencies for temporal conversions but if the language is english or something similar, this becomes a simple problem... just do the conversion and don't worry about the format. For example (and you can use CONVERT or CAST)...

<pre><code> SELECT UsingCONVERT = CONVERT(DATETIME,'10/15/2008 10:06:32 PM')
 ,UsingCAST = CAST('10/15/2008 10:06:32 PM' AS DATETIME)
;
</code></pre>

... and that produces the follow answers, both of which are correct.

<a href="https://i.stack.imgur.com/gkIZM.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/gkIZM.png" alt="enter image description here"></a>

Like they say on the TV commercials, "But wait! Don't order yet! For no extra cost, it can do MUCH more!"

Let's see the real power of temporal conversions with the DATETIME and partially examine the mistake known as DATETIME2. Check out the whacky formats that DATETIME can handle auto-magically and that DATETIME2 cannot. Run the following code and see...

<pre><code>--===== Set the language for this example.
 SET LANGUAGE ENGLISH --Same a US-English
;
--===== Use a table constructor as if it were a table for this example.
 SELECT *
 ,DateTimeCONVERT = TRY_CONVERT(DATETIME,StringDT)
 ,DateTimeCAST = TRY_CAST(StringDT AS DATETIME)
 ,DateTime2CONVERT = TRY_CONVERT(DATETIME2,StringDT)
 ,DateTime2CAST = TRY_CAST(StringDT AS DATETIME2)
 FROM (
 VALUES
 ('Same Format As In The OP' ,'12/16/2001 01:51:01 PM')
 ,('Almost Normal' ,'16 December, 2001 1:51:01 PM')
 ,('More Normal' ,'December 16, 2001 01:51:01 PM')
 ,('Time Up Front + Spaces' ,' 13:51:01 16 December 2001')
 ,('Totally Whacky Format #01' ,' 16 13:51:01 December 2001')
 ,('Totally Whacky Format #02' ,' 16 December 13:51:01 2001 ')
 ,('Totally Whacky Format #03' ,' 16 December 01:51:01 PM 2001 ')
 ,('Totally Whacky Format #04' ,' 2001 16 December 01:51:01 PM ')
 ,('Totally Whacky Format #05' ,' 2001 December 01:51:01 PM 16 ')
 ,('Totally Whacky Format #06' ,' 2001 16 December 01:51:01 PM ')
 ,('Totally Whacky Format #07' ,' 2001 16 December 13:51:01 PM ')
 ,('Totally Whacky Format #08' ,' 2001 16 13:51:01 PM December ')
 ,('Totally Whacky Format #09' ,' 13:51:01 PM 2001.12/16 ')
 ,('Totally Whacky Format #10' ,' 13:51:01 PM 2001.December/16 ')
 ,('Totally Whacky Format #11' ,' 13:51:01 PM 2001.Dec/16 ')
 ,('Totally Whacky Format #12' ,' 13:51:01 PM 2001.Dec.16 ')
 ,('Totally Whacky Format #13' ,' 13:51:01 PM 2001/Dec.16')
 ,('Totally Whacky Format #14' ,' 13:51:01 PM 2001 . 12/16 ')
 ,('Totally Whacky Format #15' ,' 13:51:01 PM 2001 . December / 16 ')
 ,('Totally Whacky Format #16' ,' 13:51:01 PM 2001 . Dec / 16 ')
 ,('Totally Whacky Format #17' ,' 13:51:01 PM 2001 . Dec . 16 ')
 ,('Totally Whacky Format #18' ,' 13:51:01 PM 2001 / Dec . 16')
 ,('Totally Whacky Format #19' ,' 13:51:01 PM 2001 . Dec - 16 ')
 ,('Totally Whacky Format #20' ,' 13:51:01 PM 2001 - Dec - 16 ')
 ,('Totally Whacky Format #21' ,' 13:51:01 PM 2001 - Dec . 16')
 ,('Totally Whacky Format #22' ,' 13:51:01 PM 2001 - Dec / 16 ')
 ,('Totally Whacky Format #23' ,' 13:51:01 PM 2001 / Dec - 16')
 ,('Just the year' ,' 2001 ')
 ,('YYYYMM' ,' 200112 ')
 ,('YYYY MMM' ,'2001 Dec')
 ,('YYYY-MMM' ,'2001-Dec')
 ,('YYYY . MMM' ,'2001 . Dec')
 ,('YYYY / MMM' ,'2001 / Dec')
 ,('YYYY - MMM' ,'2001 / Dec')
 ,('Forgot The Spaces #1' ,'2001December26')
 ,('Forgot The Spaces #2' ,'2001Dec26')
 ,('Forgot The Spaces #3' ,'26December2001')
 ,('Forgot The Spaces #4' ,'26Dec2001')
 ,('Forgot The Spaces #5' ,'26Dec2001 13:51:01')
 ,('Forgot The Spaces #6' ,'26Dec2001 13:51:01PM')
 ,('Oddly, this doesn''t work' ,'2001-12')
 ,('Oddly, this doesn''t work' ,'12-2001')
 ) v (Description,StringDT)
;
</code></pre>

So, yeah... SQL Server DOES actually have a pretty flexible method of handling all sorts of weird-o temporal formats and no special handling is required. We didn't even need to remove the "PM"s that were added to the 24 hour times. It's "PFM" (Pure Freakin' Magic).

Things will vary a bit depending on the the LANGUAGE is that you've selected for your server but a whole lot of it will be handled either way.

And these "auto-magic" conversions aren't something new. They go a real long way back.

blocks|key|1715735|text|您可以使用下面的代码轻松地实现这一点。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1715736|SELECT+Convert(日期时间，Convert(varchar(30)，'10/15/2008+10:06:32+PM',102),102)|1715737|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

You can easily achieve this by using this code.
SELECT Convert(datetime, Convert(varchar(30),'10/15/2008 10:06:32 PM',102),102)

blocks|key|1500712|text|这段代码解决了我的问题：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1500713|convert(date,YOUR_DATE,104)|code-block|syntax|javascript|1500714|如果您使用的是timestamp，您可以使用以下代码：|offset|length|style|CODE|1500715|convert(datetime,YOUR_DATE,104)|1500716|entityMap^0|0|0|7|9|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

This code solve my problem :
<pre><code>convert(date,YOUR_DATE,104)
</code></pre>
If you are using <code>timestamp</code> you can you the below code :
<pre><code>convert(datetime,YOUR_DATE,104)
</code></pre>

I want to convert a string like this:

<pre><code>'10/15/2008 10:06:32 PM'
</code></pre>

into the equivalent DATETIME value in Sql Server.

In Oracle, I would say this:

<pre><code>TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM')
</code></pre>

<a href="https://stackoverflow.com/questions/202243/custom-datetime-formatting-in-sql-server">This question</a> implies that I must parse the string into one of the <a href="http://www.sql-server-helper.com/tips/date-formats.aspx" rel="noreferrer">standard formats</a>, and then convert using one of those codes. That seems ludicrous for such a mundane operation. Is there an easier way?

Sql Server string to date conversion

SQL Server

服务器

我想像这样转换一个字符串：'10/15/2008 10:06:32 PM'转换为Sql Server中的等效DateTime值。在Oracle中，我会这样说：TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM')意味着我必须将字符串解析为其中一个，然后使用其...

问Sql Server字符串到日期的转换
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Sql Server字符串到日期的转换EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Sql Server字符串到日期的转换
EN