数据库基础数据:
查询sql:
SELECT b.ID id,
b.`NAME`,
DATE_FORMAT(b.CREATEDATE, '%Y-%m-%d %H:%m:%s')CREATEDATE,
b.stateDate,
b.cpaReward,
(
CASE
WHEN DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%m:%s') < DATE_FORMAT(b.startTime, '%Y-%m-%d %H:%m:%s') THEN
'0'
WHEN DATE_FORMAT(b.startTime, '%Y-%m-%d %H:%m:%s') <= DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%m:%s')
AND DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%m:%s') <= DATE_FORMAT(b.endTime, '%Y-%m-%d %H:%m:%s') THEN
'1'
WHEN DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%m:%s') > DATE_FORMAT(b.endTime, '%Y-%m-%d %H:%m:%s') THEN
'2'
ELSE
''
END
) state
FROM
bm_team_pull_new_config b
where 1 = 1
查询结果: