Fixing Date formats in SQL

问题: SELECT "Trade Details 2".Portfolio, "Trade Details 2".CONTRACT_ID, "Trade Details 2".START_DATE, "Trade Details 2".MATURITY_DATE, "Trade Det...

问题:

SELECT "Trade Details 2".Portfolio, 
       "Trade Details 2".CONTRACT_ID, 
       "Trade Details 2".START_DATE, 
       "Trade Details 2".MATURITY_DATE, 
       "Trade Details 2".NOTIONAL1,
       "Trade Details 2".CONTRACT_NPV,
       "Trade Details".TERM
FROM "Trade Details 2" 
JOIN "Trade Details" 
WHERE "Trade Details 2".CONTRACT_ID="Trade Details".FCC_ID and
      ("Trade Details 2".NOTIONAL1 > "0.0") and
      ("Trade Details 2".MATURITY_DATE > "20180621")

ORDER BY CONTRACT_ID asc

I'm attempting to fix the date formats for my START_DATE and MATURITY_DATE columns in the tables I've joined in this query. The format for the START_DATE is like: 2018-06-22 00:00:00, while the MATURITY_DATE is like: 20180622. I would like to be able to fix this so both columns are the same format, preferably just 2018-06-22. I have already tried in python using: input_string='20191217'

start_date=pd.to_datetime(input_string[-2:]+input_string[4:6]+ 
input_string[:4], dayfirst=True)

but that didn't work, just claiming invalid syntax. Is there a way in SQL or Python to make these two columns have the same date?


回答1:

What DBMS are you using ? You need to convert your start date by using STR_TO_DATE('your string', '%Y-%m-%d') for example.

SELECT STR_TO_DATE('2018-06-22', '%Y-%m-%d') FROM DUAL;

回答2:

EDIT: Changed query for SQLite DB

SELECT "Trade Details 2".Portfolio, 
       "Trade Details 2".CONTRACT_ID, 
       DATE("Trade Details 2".START_DATE) as START_DATE, 
       DATE(substr("Trade Details 2".MATURITY_DATE, 0, 5) || '-' || substr("Trade Details 2".MATURITY_DATE, 5,2) || '-' ||    substr("Trade Details 2".MATURITY_DATE, 7, 9)) as MATURITY_DATE, 
       "Trade Details 2".NOTIONAL1,
       "Trade Details 2".CONTRACT_NPV,
       "Trade Details".TERM
FROM "Trade Details 2" 
JOIN "Trade Details" 
WHERE "Trade Details 2".CONTRACT_ID="Trade Details".FCC_ID and
      ("Trade Details 2".NOTIONAL1 > "0.0") and
      ("Trade Details 2".MATURITY_DATE > DATE(substr('20180602', 0, 5) || '-' || substr('20180602', 5,2) || '-' ||    substr('20180602', 7, 9)) )

ORDER BY CONTRACT_ID asc
  • 发表于 2018-07-10 14:17
  • 阅读 ( 233 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除