将varchar转换为datetime以排序日期？

问题描述：

I want to try sorting dates but there are constraints, because those columns are varchar.

This existing data

enter image description here

This is the query that I test.

SELECT        PERIODE, RF, APPROVE_DATE
FROM            DLY_UPGRADE
WHERE        (SLP = '368') AND (CONVERT(datetime, APPROVE_DATE, 101) > '01/01/2017')

The error message :

enter image description here

我想尝试排序日期但有约束，因为这些列是varchar。 p>

此现有数据 p>

这是我测试的查询。 p>

  SELECT PERIODE，RF，APPROVE_DATE 
FROM DLY_UPGRADE 
WHERE（  SLP ='368'）AND（CONVERT（datetime，APPROVE_DATE，101）＆gt; '01 / 01/2017'）
  code>  pre> 
 
 错误消息： p  > 
 
 
  p> 
  div>

答

If you are using SQL Server, try the following:

SELECT        PERIODE, RF, APPROVE_DATE
FROM            DLY_UPGRADE
WHERE        (SLP = '368') AND (CONVERT(date, APPROVE_DATE, 103) > '2017-01-01')

答

Use

 DATE_FORMAT(STR_TO_DATE(`APPROVE_DATE`, '%d/%m/%Y'), '%Y-%m-%d')

To change date from string and then compare with Y-m-d format

 SELECT        PERIODE, RF, APPROVE_DATE
 FROM            DLY_UPGRADE
 WHERE        (SLP = '368') AND (DATE_FORMAT(STR_TO_DATE(`APPROVE_DATE`, '%d/%m/%Y'), '%Y-%m-%d')) > '2017-01-01')

答

SELECT        PERIODE, RF, APPROVE_DATE, cast(PERIODE as datetime) as PERIODEDatetime
FROM            DLY_UPGRADE
WHERE        (SLP = '368') AND (CONVERT(datetime, APPROVE_DATE, 101) > '01/01/2017')
order by PERIODEDatetime asc

Here you can cast your varchar to datetime and use that to order in asc/desc. It's the same thing for APPROVE_DATE as well.

答

Your conversion problem would appear to be in the WHERE clause. But, before proceeding with the query, I would test using TRY_CONVERT():

select approve_date
from dly_upgrade
where slp = '368' and approve_date is not null and
      try_convert(date, approve_date, 103) is null;

The style 101 is MM/DD/YYYY, so '15/11/2016' is going to fail because 15 is not a valid month. 103 is DD/MM/YYYY, which appears to be what you want.

Assuming that this returns no unexpected values in the column, I would suggest:

SELECT PERIODE, RF, APPROVE_DATE
FROM  DLY_UPGRADE
WHERE SLP = '368' AND
      TRY_CONVERT(datetime, APPROVE_DATE, 103) > '2017-01-01'
ORDER BY ?  -- not sure if you want PERIOD or TRY_CONVERT(datetime, APPROVE_DATE, 103)

将varchar转换为datetime以排序日期？

相关推荐