dseigqk7443
2019-07-05 02:42
浏览 676
已采纳

将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

图片转代码服务由CSDN问答提供 功能建议

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

此现有数据

这是我测试的查询。

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

错误消息:

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • doushui3061 2019-07-05 04:13
    已采纳

    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')
    
    已采纳该答案
    打赏 评论
  • duankuang7928 2019-07-05 03:34

    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')
    
    打赏 评论
  • dongpu5600 2019-07-05 11:07
    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.

    打赏 评论
  • duanqiao1947 2019-07-05 11:41

    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)
    
    打赏 评论

相关推荐 更多相似问题