字符串转换日期解决方案-MSSQL教程-爱易网页

字符串转换日期解决方案

日期：2014-05-18　浏览次数：20494 次

字符串转换日期

19th May 22:30

2012-5-19 22:30

------解决方案--------------------

SQL code


declare @date varchar(20)
set @date='04th Dec 19:00'
select '2012-'+case 
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Jan' then '01-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Feb' then '02-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Mar' then '03-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Apr' then '04-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='May' then '05-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Jun' then '06-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Jul' then '07-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Aug' then '08-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Sep' then '09-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Nov' then '10-'
    when rtrim(ltrim(SUBSTRING(@date,CHARINDEX(' ',@date),4)))='Dec' then '11-'
    else '12-' end+right('00'+LEFT(@date,CHARINDEX('t',@date)-1),2)+RIGHT(@date,6) as 'date'
/*
date
2012-11-04 19:00
*/