sql server CONVERT函数转换为mysql的语句

码拜

8年 ago

SELECT 
  a.tID as saleTime,
  RIGHT(
      CONVERT(VARCHAR (13), B.operDate, 121),
      2
    )  as aaa,
  SUM(
    CASE
      WHEN B.sellWay = "A" 
      THEN B.saleAmt 
      WHEN B.sellWay = "B" 
      THEN 0- B.saleAmt 
      ELSE 0 
    END
  ) AS sellAmt,
  COUNT(
    DISTINCT 
    CASE
      WHEN (
        B.sellway = "A" 
        AND isnull(B.RtnFlowNo, "") = ""
      ) 
      THEN B.flowno 
      ELSE NULL 
    END
  ) AS sellTime,
  (
    CASE
      WHEN COUNT(
        DISTINCT 
        CASE
          WHEN (
            B.sellway = "A" 
            AND isnull(B.RtnFlowNo, "") = ""
          ) 
          THEN B.flowno 
          ELSE NULL 
        END
      ) = 0 
      THEN 0 
      ELSE SUM(
        CASE
          WHEN B.sellWay = "A" 
          THEN B.saleAmt 
          WHEN B.sellWay = "B" 
          THEN 0- B.saleAmt 
          ELSE 0 
        END
      ) / COUNT(
        DISTINCT 
        CASE
          WHEN (
            B.sellway = "A" 
            AND isnull(B.RtnFlowNo, "") = ""
          ) 
          THEN B.flowno 
          ELSE NULL 
        END
      ) 
    END
  ) AS avAmt,
  SUM(
    CASE
      WHEN B.sellWay = "A" 
      THEN B.custNumber 
      WHEN B.sellWay = "B" 
      THEN - B.custNumber 
      ELSE 0 
    END
  ) AS custNumber,
  (
    CASE
      WHEN SUM(
        CASE
          WHEN B.sellWay = "A" 
          THEN B.custNumber 
          WHEN B.sellWay = "B" 
          THEN - B.custNumber 
          ELSE 0 
        END
      ) = 0 
      THEN 0 
      ELSE SUM(
        CASE
          WHEN B.sellWay = "A" 
          THEN B.saleAmt 
          WHEN B.sellWay = "B" 
          THEN 0- B.saleAmt 
          ELSE 0 
        END
      ) / SUM(
        CASE
          WHEN B.sellWay = "A" 
          THEN B.custNumber 
          WHEN B.sellWay = "B" 
          THEN - B.custNumber 
          ELSE 0 
        END
      ) 
    END
  ) AS avCustAmt 
FROM
  ts_time A 
  LEFT JOIN pos_tranpay B 
    ON --CONVERT(DATETIME, B.operDate)
    B.operDate BETWEEN "2016-09-20 0:0:0.000" 
    AND "2016-10-09 23:59:59.900" 
    AND B.WarehouseID = "00" 
    AND A.tID = RIGHT(
      CONVERT(VARCHAR (13), B.operDate, 121),
      2
    )
GROUP BY A.tID,B.operDate
ORDER BY A.tID

结果：

也就是说把时间戳的类型转换为了varchar，假如语句复制到mysql，这段语句应该怎么样修改？

解决方案

RIGHT(
      CONVERT(VARCHAR (13), B.operDate, 121),
      2
    )

改为

time_format(B.operDate,"%H")

或

date_format(now(),"%H")

引用 MySQL 5.7 Reference Manual

DATE_FORMAT(date,format)
Formats the date value according to the format string.
The following specifiers may be used in the format string. The “%” character is required before format specifier characters.
Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character
%x x, for any “x” not listed above
Ranges for the month and day specifiers begin with zero due to the fact that MySQL allows the storing of incomplete dates such as “2014-00-00”.
The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable (Section 9.8, “MySQL Server Locale Support”).
DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters.
mysql> SELECT DATE_FORMAT(“2009-10-04 22:23:00”, “%W %M %Y”);
-> “Sunday October 2009”
mysql> SELECT DATE_FORMAT(“2007-10-04 22:23:00”, “%H:%i:%s”);
-> “22:23:00”
mysql> SELECT DATE_FORMAT(“1900-10-04 22:23:00”,
-> “%D %y %a %d %m %b %j”);
-> “4th 00 Thu 04 10 Oct 277”
mysql> SELECT DATE_FORMAT(“1997-10-04 22:23:00”,
-> “%H %k %I %r %T %S %w”);
-> “22 22 10 10:23:00 PM 22:23:00 00 6”
mysql> SELECT DATE_FORMAT(“1999-01-01”, “%X %V”);
-> “1998 52”
mysql> SELECT DATE_FORMAT(“2006-06-00”, “%d”);
-> “00”