SQL 将非标准日期格式转换成标准格式,进行条件判断

a.JLDate为非标准日期格式：

例：

2011-8-28 0:00:00
0011-8-28 0:00:00
0111-8-4 0:00:00

 

select CONVERT(varchar(50),CONVERT(datetime, a.JLDate, 120),120) as JLDate,b.ch,b.bh,
cast((cast(b.mz as decimal(18,6))/1000) as decimal(18,6)) as mz,a.ExeCode as zzdm
from oil_JLHead a
left join oil_JLItem b on a.JLHead_ID = b.JLHead_ID and a.ExeCode = b.ExeCode
where ISDATE(a.JLDate)=1 and a.ExeCode='0050030101' and CONVERT(datetime, case WHEN ISDATE(a.JLDate)=1 THEN a.JLDate else '1777-01-01' end, 120) between '2011-05-13 00:00:00' and '2011-05-21 00:00:00'
order by CONVERT(datetime, a.JLDate, 120)