sql server中'14-02-2001 00:00:00'和'1-01-1 00:00:00'格式的varchar如何转换为datetime

[iery]

sql server中'14-02-2001 00:00:00'和'1-01-1 00:00:00'格式的varchar如何转换为datetime

[pbsql]

'14-02-2001 00:00:00'，将月、日位置交换即变成'02-14-2001 00:00:00'再转换

'1-01-1 00:00:00'，举的例子不恰当，不知道哪是年、月、日

[paoluo]

Declare @S Varchar(20)
Set @S='14-02-2001 00:00:00'
Select Cast(SubString(@S,CharIndex('-',@S,CharIndex('-',@S)+1)+1,CharIndex(' ',@S)-CharIndex('-',@S,CharIndex('-',@S)+1)-1)+'-'+SubString(@S,CharIndex('-',@S)+1,CharIndex('-',@S,CharIndex('-',@S)+1)-CharIndex('-',@S)-1)+'-'+Left(@S,CharIndex('-',@S)-1)+Stuff(@S,1,CharIndex(' ',@S)-1,'') As DateTime)
--结果
2001-02-14 00:00:00.000

[子陌红尘]

1、

declare @d varchar(20)
set @d = '14-02-2001 00:00:00'
select cast(substring(@d,7,4)+'-'+substring(@d,4,3)+left(@d,2)+right(@d,9) as datetime)

[wpmxm]

cast, convert
结合其它一些函数

[mengzulin]

Declare @S Varchar(20)
Set @S='14-02-2001 00:00:00'

SELECT CHARINDEX('-',@S)
SELECT cast(SUBSTRING(@S,CHARINDEX('-',@S)+1,2)+'-'+SUBSTRING(@S,1,CHARINDEX('-',@S)-1)+right(@S,len(@S)-CHARINDEX('-',@S,CHARINDEX('-',@S)+1)+1) as datetime)