前言：
此时咱们对“oracle中把日期转换成星期”大概比较看重，姐妹们都需要了解一些“oracle中把日期转换成星期”的相关内容。那么小编也在网上收集了一些关于“oracle中把日期转换成星期””的相关知识，希望小伙伴们能喜欢，大家快快来学习一下吧！/**
* 根据数据库类型获取时间转换函数
*
* @param databaseType
* @param columnName
* @param stageType
* @return
*/
private static String getFunctionByDatabaseTypeAndStageType(String databaseType, String columnName, Integer type) {
if (null == databaseType) {
return null;
}
StageTypeEnum stageType = StageTypeEnum.ordinalOf(type);
if (stageType == null) {
throw new AudaqueException("分期类型不能为null");
}
if (databaseType.equalsIgnoreCase(DatabaseType.Oracle.getName()) || databaseType.equalsIgnoreCase(DatabaseType.GaussDB100.getName())) {
if (StageTypeEnum.TYPE_DAY.equals(stageType)) {
return "to_char(" + columnName + ",'YYYY-MM-DD')";
}
if (StageTypeEnum.TYPE_WEEK.equals(stageType)) {
// TO_CHAR(trunc(TO_DATE('2017-01-01','YYYY-MM-DD'), 'IW'),'YYYYIW')
return "to_char(trunc(" + columnName + ", 'IW'),'YYYY-IW')";
}
if (StageTypeEnum.TYPE_MONTH.equals(stageType)) {
return "to_char(" + columnName + ",'YYYY-MM')";
}
if (StageTypeEnum.TYPE_QUARTER.equals(stageType)) {
return "to_char(" + columnName + ",'YYYY-Q')";
}
if (StageTypeEnum.TYPE_YEAR.equals(stageType)) {
return "to_char(" + columnName + ",'YYYY')";
}
} else if (databaseType.equalsIgnoreCase(DatabaseType.MySQL.getName()) || databaseType.equalsIgnoreCase(DatabaseType.GBase.getName())) {
// mysql DATE_FORMAT(time,formate);
if (StageTypeEnum.TYPE_DAY.equals(stageType)) {
return "DATE_FORMAT(" + columnName + ",'%Y-%m-%d')";
}
if (StageTypeEnum.TYPE_WEEK.equals(stageType)) {
return "DATE_FORMAT(" + columnName + ",'%x-%v')";
}
if (StageTypeEnum.TYPE_MONTH.equals(stageType)) {
return "DATE_FORMAT(" + columnName + ",'%Y-%m')";
}
if (StageTypeEnum.TYPE_QUARTER.equals(stageType)) {
//SELECT concat(date_format(CREATE_TIME, '%Y-'),FLOOR((date_format(CREATE_TIME, '%m')+2)/3)),CREATE_TIME,count(1) datasize FROM adqm_job_report GROUP BY concat(date_format(CREATE_TIME, '%Y '),FLOOR((date_format(CREATE_TIME, '%m ')+2)/3)) ORDER BY CREATE_TIME
return "concat(date_format(" + columnName + ", '%Y-0'),FLOOR((date_format(" + columnName + ", '%m')+2)/3))";
}
if (StageTypeEnum.TYPE_YEAR.equals(stageType)) {
return "DATE_FORMAT(" + columnName + ",'%Y')";
}
} else if (databaseType.equalsIgnoreCase(DatabaseType.SQLServer.getName())) {
// CONVERT(data_type(length),data_to_be_converted,style)
if (StageTypeEnum.TYPE_DAY.equals(stageType)) {
return "CONVERT(VARCHAR(10)," + columnName + ",20)";
}
if (StageTypeEnum.TYPE_WEEK.equals(stageType)) {
throw new AudaqueException("SQLServer数据库类型不建议按星期分组");
}
if (StageTypeEnum.TYPE_MONTH.equals(stageType)) {
return "CONVERT(VARCHAR(7)," + columnName + ",20)";
}
if (StageTypeEnum.TYPE_QUARTER.equals(stageType)) {
throw new AudaqueException("SQLServer数据库类型不建议按季度分组");
}
if (StageTypeEnum.TYPE_YEAR.equals(stageType)) {
return "CONVERT(VARCHAR(4)," + columnName + ",20)";
}
} else if (databaseType.equalsIgnoreCase(DatabaseType.DB2.getName())) {
throw new AudaqueException("暂时不支持DB2数据库分组");
} else if (databaseType.equalsIgnoreCase(DatabaseType.PostgreSQL.getName())
|| databaseType.equalsIgnoreCase(DatabaseType.GaussDB200.getName())
|| databaseType.equalsIgnoreCase(DatabaseType.HWMPPDB.getName())
|| databaseType.equalsIgnoreCase(DatabaseType.GreenPlum.getName())) {
// 华为mpp 与 GreenPlum都是基于postgresql,可复用 字段须加"号，否则语法不对
// PostgreSQL to_char(time,formate);
if (StageTypeEnum.TYPE_DAY.equals(stageType)) {
return "to_char(\"" + columnName + "\",'YYYY-MM-DD')";
}
if (StageTypeEnum.TYPE_WEEK.equals(stageType)) {
// to_char(date_trunc('week', DATE '2017-01-01'),'YYYY-WW')
return "to_char(date_trunc('week', \"" + columnName + "\"),'YYYY-WW')";
}
if (StageTypeEnum.TYPE_MONTH.equals(stageType)) {
return "to_char(\"" + columnName + "\",'YYYY-MM')";
}
if (StageTypeEnum.TYPE_QUARTER.equals(stageType)) {
// to_char(date_trunc('QUARTER', DATE '2017-10-30'),'YYYY-Q')
return "to_char(date_trunc('QUARTER', \"" + columnName + "\"),'YYYY-Q')";
}
if (StageTypeEnum.TYPE_YEAR.equals(stageType)) {
return "to_char(\"" + columnName + "\",'YYYY')";
}
} else if (databaseType.equalsIgnoreCase(DatabaseType.SQLite.getName())) {
throw new AudaqueException("暂时不支持SQLite数据库分组");
} else if (databaseType.equalsIgnoreCase(DatabaseType.BDB.getName())) {
throw new AudaqueException("暂时不支持BDB数据库分组");
} else if (databaseType.equalsIgnoreCase(DatabaseType.Sybase.getName())) {
throw new AudaqueException("暂时不支持Sybase数据库分组");
} else if (databaseType.equalsIgnoreCase(DatabaseType.Hive.getName())) {
throw new AudaqueException("暂时不支持Hive数据库分组");
} else if (databaseType.equalsIgnoreCase(DatabaseType.ALHive.getName())) {
throw new AudaqueException("暂时不支持ALHive数据库分组");
} else if (databaseType.equalsIgnoreCase(DatabaseType.INCEPTOR.getName())) {
/**
* 星环数据库适配
*/
if (StageTypeEnum.TYPE_DAY.equals(stageType)) {
return "to_date(" + columnName + ",'YYYY-MM-dd')";
}
if (StageTypeEnum.TYPE_WEEK.equals(stageType)) {
// TO_CHAR(trunc(TO_DATE('2017-01-01','YYYY-MM-DD'), 'IW'),'YYYYIW')
return "CONCAT(year(" + columnName + "),'-',weekofyear(" + columnName + "))";
}
if (StageTypeEnum.TYPE_MONTH.equals(stageType)) {
return "to_date(" + columnName + ",'YYYY-MM')";
}
if (StageTypeEnum.TYPE_QUARTER.equals(stageType)) {
return "CONCAT(year(" + columnName + "),'-',FLOOR(month(" + columnName + ")/3+1))";
}
if (StageTypeEnum.TYPE_YEAR.equals(stageType)) {
return "year(" + columnName + ")";
}
} else if (databaseType.equalsIgnoreCase(DatabaseType.Teradata.getName())) {
throw new AudaqueException("暂时不支持Teradata数据库分组");
} else if (databaseType.equalsIgnoreCase(DatabaseType.HWBigdata.getName())) {
if (StageTypeEnum.TYPE_DAY.equals(stageType)) {
return "to_date(" + columnName + ")";
}
if (StageTypeEnum.TYPE_WEEK.equals(stageType)) {
// TO_CHAR(trunc(TO_DATE('2017-01-01','YYYY-MM-DD'), 'IW'),'YYYYIW')
return "CONCAT(year(" + columnName + "),'-',weekofyear(" + columnName + "))";
}
if (StageTypeEnum.TYPE_MONTH.equals(stageType)) {
return "CONCAT(year(" + columnName + "),'-',month(" + columnName + "))";
}
if (StageTypeEnum.TYPE_QUARTER.equals(stageType)) {
return "CONCAT(year(" + columnName + "),'-',month(" + columnName + ")/3+1)";
}
if (StageTypeEnum.TYPE_YEAR.equals(stageType)) {
return "year(" + columnName + ")";
}
}
return null;
}
标签： #oracle中把日期转换成星期 #java获取季度 #java获取季度初日期
