mysql
|
private String UPDATETIME = "UPDATE " + newSchema + //"UMP_SA" ".SA_INCOMINGMSGLOG AS a, " + oldSchema + //"SMB_SA" ".SA_MSGTRACETIME AS b " + "SET a.RCVTIME = " + ConvertDateType.toDate(dbm.getDB_TYPE(), "b", "MSGRECEIVETIME") + ", " + "a.ACKSENDTIME = " + ConvertDateType.toDate(dbm.getDB_TYPE(), "b", "MSGACKRECEIVETIME") + "WHERE a.MSGID = b.MSGID AND b.MSGRECEIVETIME IS NOT NULL"; |
oracle
|
private String UPDATETIME_ORACLE = "UPDATE " + newSchema + //"UMP_SA" ".SA_INCOMINGMSGLOG a " + "SET (RCVTIME,ACKSENDTIME) = (SELECT TO_TIMESTAMP( b.RCVTIME, 'YYYYMMDDHH24MISSFF3')," + "TO_TIMESTAMP( b.ACKSENDTIME, 'YYYYMMDDHH24MISSFF3') " + "FROM " + oldSchema + //"SMB_SA" ".SA_INCOMINGMSGLOG b " + "WHERE a.MSGID = b.MSGID);"; |
mssql
|
private String UPDATETIME_MSSQL = "UPDATE a SET" + "a.RCVTIME = " + ConvertDateType.toDate(dbm.getDB_TYPE(), "b", "MSGRECEIVETIME") + ", " + "a.ACKSENDTIME = " + ConvertDateType.toDate(dbm.getDB_TYPE(), "b", "MSGACKRECEIVETIME") + "FROM " + newSchema + //"UMP_SA" ".SA_INCOMINGMSGLOG a, " + ".SA_MSGTRACETIME b " + "WHERE a.MSGID = b.MSGID AND b.MSGRECEIVETIME IS NOT NULL"; |
Time convert
|
public static String toDate(String dbType, String table, String column) { String query = ""; try { if(dbType.equalsIgnoreCase("mysql")) { query += " (SELECT STR_TO_DATE(" + table + "." + column + ", '%Y%m%d%H%i%S%f' ) ) "; } else if(dbType.equalsIgnoreCase("oracle")) { query += " (SELECT TO_TIMESTAMP(" + table + "." + column + ", 'YYYYMMDDHH24MISSFF3' ) ) "; } else if(dbType.equalsIgnoreCase("mssql")) { query += " (SELECT CONVERT(DATETIME, LEFT(" + table + "." + column + ",8) + ' ' + " + "SUBSTRING(" + table + "." + column + ",9,2) + ':' + " + "SUBSTRING(" + table + "." + column + ",11,2) + ':' + " + "SUBSTRING(" + table + "." + column + ",13,2) + ':' + " + "SUBSTRING(" + table + "." + column + ",15,3) )"; } } catch (Exception e) { e.printStackTrace(); query = "0"; }
return query; } |