在sql server中转换字符串到日期。

How do I convert YYYY-MM-DD (2012-08-17) to a date in SQL Server?

如何在SQL Server中将yyyyy - mm - dd(2012-08-17)转换为日期?

I don't see this format listed on the help page: http://msdn.microsoft.com/en-us/library/ms187928.aspx

我没有看到在help页面上列出的这种格式:http://msdn.microsoft.com/en-us/library/ms187928.aspx。

4 个解决方案

#1

I think style no. 111 (Japan) should work:

我觉得风格不。111年(日本)工作:

SELECT CONVERT(DATETIME, '2012-08-17', 111)

And if that doesn't work for some reason - you could always just strip out the dashes and then you have the totally reliable ISO-8601 format (YYYYMMDD) which works for any language and date format setting in SQL Server:

如果因为某种原因不能工作，你可以去掉破折号，然后你就有了完全可靠的ISO-8601格式(YYYYMMDD)，它适用于SQL Server中的任何语言和日期格式设置:

SELECT CAST(REPLACE('2012-08-17', '-', '') AS DATETIME)

#2

This will do the trick:

这将会达到目的:

SELECT CONVERT(char(10), GetDate(),126)

#3

I had a similar situation. Here's what I was able to do to get a date range in a "where" clause (a modification of marc_s's answer):

我也有类似的情况。下面是我在“where”子句(marc_s答案的修改)中获得日期范围的方法:

where cast(replace(foo.TestDate, '-', '') as datetime) 
      between cast('20110901' as datetime) and
              cast('20510531' as datetime)

Hope that helps...

希望这有助于……

#4

if you datatype is datetime of the table.col , then database store data contain two partial : 1 (date) 2 (time)

如果数据类型是表的datetime。col，则数据库存储数据包含两个部分:1(日期)2(时间)

Just in display data use convert or cast.

仅显示数据使用转换或转换。

Example:

例子:

create table #test(part varchar(10),lastTime datetime)
go

insert into #test (part ,lastTime )
values('A','2012-11-05 ')

insert into #test (part ,lastTime )
values('B','2012-11-05 10:30')


go

select * from #test 

A   2012-11-05 00:00:00.000
B   2012-11-05 10:30:00.000

select part,CONVERT (varchar,lastTime,111) from #test

A   2012/11/05
B   2012/11/05

select part,CONVERT (varchar(10),lastTime,20) from #test 

A   2012-11-05
B   2012-11-05

#1