valid date

 

	public final static boolean isValidDate(final String date, final String format) {

		boolean result = false;
		try{
			Date d = toDate(date, format);
			String s = DateFormatUtil.format(d, format);
			result = s.equals(date);
		} catch (Exception ex) {
		}
		return result;
	}

	public final static boolean isValidDate(final String date, final String format) {
		boolean result = false;
		try{
			Date d = toDate(date, format);
			String s = DateFormatUtil.format(d, format);
			result = s.equals(date);
		} catch (Exception ex) {
		}
		return result;
	}

 

invalid date: 20170230,20170232

 

 

 if(!DateUtil.isValidDate(dateString, SDConstant.DK_ASSESSMENT_INFO_IMPORT_DKREVIEW_DATE_FORMAT)){
    throw new AppException(ErrorCodeConstant.DK_ASSESSMENT_IMPORT_DATE_PARSING_ERROR, new String[]{dateString, String.valueOf(dataSet.getRowNo())},null);
}

 

 

-- 曲轴材料不良率分析(修复UNION排序问题) WITH -- 日期验证CTE(为每个表单独创建) DateValidation_b AS ( SELECT [日期] AS 原始日期, CASE WHEN ISDATE([日期]) = 1 THEN TRY_CAST([日期] AS DATE) WHEN ISDATE(REPLACE([日期], '/', '-')) = 1 THEN TRY_CAST(REPLACE([日期], '/', '-') AS DATE) ELSE NULL END AS ValidDate FROM [dbo].[V_报废不良] ), DateValidation_p AS ( SELECT [日期] AS 原始日期, CASE WHEN ISDATE([日期]) = 1 THEN TRY_CAST([日期] AS DATE) WHEN ISDATE(REPLACE([日期], '/', '-')) = 1 THEN TRY_CAST(REPLACE([日期], '/', '-') AS DATE) ELSE NULL END AS ValidDate FROM [dbo].[V_加工线每月生产台数] ), -- 2025年月度数据 MonthlyData2025 AS ( SELECT FORMAT(dv_b.ValidDate, 'yyyy-MM') AS 年月, SUM(CONVERT(INT, b.数量)) AS 不良数量, SUM(p.实绩) AS 生产台数, -- 添加排序辅助列 FORMAT(dv_b.ValidDate, 'yyyy-MM') AS 原始年月, 2 AS SortOrder -- 月度数据排序值 FROM [dbo].[V_报废不良] b INNER JOIN DateValidation_b dv_b ON b.日期 = dv_b.原始日期 AND dv_b.ValidDate IS NOT NULL INNER JOIN [dbo].[V_加工线每月生产台数] p INNER JOIN DateValidation_p dv_p ON p.日期 = dv_p.原始日期 AND dv_p.ValidDate IS NOT NULL ON dv_b.ValidDate >= DATEADD(DAY, 1, EOMONTH(dv_p.ValidDate, -1)) AND dv_b.ValidDate <= EOMONTH(dv_p.ValidDate) AND p.线别 = '曲轴' WHERE b.生产线 = '曲轴' AND LEFT(b.报废原因, 4) = '材料不良' AND b.产线 = 'TNGA12' AND YEAR(dv_b.ValidDate) = 2025 GROUP BY FORMAT(dv_b.ValidDate, 'yyyy-MM'), dv_b.ValidDate ), -- 2025年季度数据 QuarterlyData2025 AS ( SELECT CONCAT('Q', DATEPART(QUARTER, dv_b.ValidDate), '-', YEAR(dv_b.ValidDate)) AS 年月, SUM(CONVERT(INT, b.数量)) AS 不良数量, SUM(p.实绩) AS 生产台数, -- 添加排序辅助列 CONVERT(VARCHAR(7), dv_b.ValidDate, 120) AS 原始年月, -- 保留年月信息 1 AS SortOrder -- 季度数据排序值 FROM [dbo].[V_报废不良] b INNER JOIN DateValidation_b dv_b ON b.日期 = dv_b.原始日期 AND dv_b.ValidDate IS NOT NULL INNER JOIN [dbo].[V_加工线每月生产台数] p INNER JOIN DateValidation_p dv_p ON p.日期 = dv_p.原始日期 AND dv_p.ValidDate IS NOT NULL ON dv_b.ValidDate >= DATEADD(DAY, 1, EOMONTH(dv_p.ValidDate, -1)) AND dv_b.ValidDate <= EOMONTH(dv_p.ValidDate) AND p.线别 = '曲轴' WHERE b.生产线 = '曲轴' AND LEFT(b.报废原因, 4) = '材料不良' AND b.产线 = 'TNGA12' AND YEAR(dv_b.ValidDate) = 2025 GROUP BY YEAR(dv_b.ValidDate), DATEPART(QUARTER, dv_b.ValidDate), dv_b.ValidDate ), -- 2023-2024年度平均数据 YearlyAvgData AS ( SELECT CONCAT(years.年度, '平均') AS 年月, AVG(COALESCE(bad_data.月不良数量, 0)) AS 不良数量, AVG(COALESCE(prod_data.月生产台数, 0)) AS 生产台数, -- 添加排序辅助列 CONCAT(years.年度, '01') AS 原始年月, -- 伪日期用于排序 0 AS SortOrder -- 年度平均数据排序值 FROM ( SELECT 2023 AS 年度 UNION ALL SELECT 2024 ) years LEFT JOIN ( SELECT YEAR(dv_b.ValidDate) AS 年度, MONTH(dv_b.ValidDate) AS 月份, SUM(CONVERT(INT, b.数量)) AS 月不良数量 FROM [dbo].[V_报废不良] b INNER JOIN DateValidation_b dv_b ON b.日期 = dv_b.原始日期 WHERE dv_b.ValidDate IS NOT NULL AND b.生产线 = '曲轴' AND LEFT(b.报废原因, 4) = '材料不良' AND b.产线 = 'TNGA12' AND YEAR(dv_b.ValidDate) IN (2023, 2024) GROUP BY YEAR(dv_b.ValidDate), MONTH(dv_b.ValidDate) ) bad_data ON years.年度 = bad_data.年度 LEFT JOIN ( SELECT YEAR(dv_p.ValidDate) AS 年度, MONTH(dv_p.ValidDate) AS 月份, SUM(p.实绩) AS 月生产台数 FROM [dbo].[V_加工线每月生产台数] p INNER JOIN DateValidation_p dv_p ON p.日期 = dv_p.原始日期 WHERE dv_p.ValidDate IS NOT NULL AND p.线别 = '曲轴' AND YEAR(dv_p.ValidDate) IN (2023, 2024) GROUP BY YEAR(dv_p.ValidDate), MONTH(dv_p.ValidDate) ) prod_data ON years.年度 = prod_data.年度 AND bad_data.月份 = prod_data.月份 GROUP BY years.年度 ) -- 最终结果集(正确排序) SELECT 年月, '材料不良-曲轴毛坯无齿' AS 报废原因, 不良数量, 生产台数, CASE WHEN 生产台数 > 0 THEN ROUND((不良数量 * 100.0) / 生产台数, 4) ELSE 0 END AS 不良率, CASE SortOrder WHEN 0 THEN '年度平均' WHEN 1 THEN '季度' WHEN 2 THEN '月度' END AS 数据类型 FROM ( -- 合并所有数据集 SELECT 年月, 不良数量, 生产台数, 原始年月, SortOrder FROM MonthlyData2025 UNION ALL SELECT 年月, 不良数量, 生产台数, 原始年月, SortOrder FROM QuarterlyData2025 UNION ALL SELECT 年月, 不良数量, 生产台数, 原始年月, SortOrder FROM YearlyAvgData ) combined -- 使用SELECT列表中的列进行排序 ORDER BY SortOrder, -- 排序优先级(0=年度平均,1=季度,2=月度) CASE WHEN SortOrder = 0 THEN 原始年月 -- 年度平均按年份排序 ELSE TRY_CAST(原始年月 + '-01' AS DATE) -- 转换为日期排序 END DESC; 报错:从字符串转换日期和/或时间时,转换失败
最新发布
07-23
-- 最终修正版:曲轴材料不良率分析(带日期验证和别名作用域修复) WITH -- 日期验证CTE(为每个表单独创建) DateValidation_b AS ( -- 报废不良表日期验证 SELECT [日期] AS 原始日期, CASE WHEN ISDATE([日期]) = 1 THEN TRY_CAST([日期] AS DATE) WHEN ISDATE(REPLACE([日期], '/', '-')) = 1 THEN TRY_CAST(REPLACE([日期], '/', '-') AS DATE) ELSE NULL END AS ValidDate FROM [dbo].[V_报废不良] ), DateValidation_p AS ( -- 生产台数表日期验证 SELECT [日期] AS 原始日期, CASE WHEN ISDATE([日期]) = 1 THEN TRY_CAST([日期] AS DATE) WHEN ISDATE(REPLACE([日期], '/', '-')) = 1 THEN TRY_CAST(REPLACE([日期], '/', '-') AS DATE) ELSE NULL END AS ValidDate FROM [dbo].[V_加工线每月生产台数] ), -- 2025年月度数据(带日期验证) MonthlyData2025 AS ( SELECT CONVERT(VARCHAR(7), dv_b.ValidDate, 120) AS 年月, '材料不良-曲轴毛坯无齿' AS 报废原因, SUM(CONVERT(INT, b.数量)) AS 不良数量, MAX(p.实绩) AS 生产台数 FROM [dbo].[V_报废不良] b INNER JOIN DateValidation_b dv_b ON b.日期 = dv_b.原始日期 AND dv_b.ValidDate IS NOT NULL INNER JOIN [dbo].[V_加工线每月生产台数] p INNER JOIN DateValidation_p dv_p ON p.日期 = dv_p.原始日期 AND dv_p.ValidDate IS NOT NULL ON CONVERT(VARCHAR(7), dv_b.ValidDate, 120) = CONVERT(VARCHAR(7), dv_p.ValidDate, 120) AND p.线别 = '曲轴' WHERE b.生产线 = '曲轴' AND LEFT(b.报废原因, 4) = '材料不良' AND b.产线 = 'TNGA12' AND YEAR(dv_b.ValidDate) = 2025 GROUP BY CONVERT(VARCHAR(7), dv_b.ValidDate, 120) ), -- 2023-2024年度平均数据(带日期验证) YearlyAvgData AS ( SELECT CONCAT(years.年度, '平均') AS 年月, '材料不良-曲轴毛坯无齿' AS 报废原因, AVG(COALESCE(bad_data.月不良数量, 0)) AS 不良数量, AVG(COALESCE(prod_data.月生产台数, 0)) AS 生产台数 FROM ( SELECT 2023 AS 年度 UNION ALL SELECT 2024 ) years LEFT JOIN ( SELECT YEAR(dv_b.ValidDate) AS 年度, MONTH(dv_b.ValidDate) AS 月份, SUM(CONVERT(INT, b.数量)) AS 月不良数量 FROM [dbo].[V_报废不良] b INNER JOIN DateValidation_b dv_b ON b.日期 = dv_b.原始日期 WHERE dv_b.ValidDate IS NOT NULL AND b.生产线 = '曲轴' AND LEFT(b.报废原因, 4) = '材料不良' AND b.产线 = 'TNGA12' AND YEAR(dv_b.ValidDate) IN (2023, 2024) GROUP BY YEAR(dv_b.ValidDate), MONTH(dv_b.ValidDate) ) bad_data ON years.年度 = bad_data.年度 LEFT JOIN ( SELECT YEAR(dv_p.ValidDate) AS 年度, MONTH(dv_p.ValidDate) AS 月份, SUM(p.实绩) AS 月生产台数 FROM [dbo].[V_加工线每月生产台数] p INNER JOIN DateValidation_p dv_p ON p.日期 = dv_p.原始日期 WHERE dv_p.ValidDate IS NOT NULL AND p.线别 = '曲轴' AND YEAR(dv_p.ValidDate) IN (2023, 2024) GROUP BY YEAR(dv_p.ValidDate), MONTH(dv_p.ValidDate) ) prod_data ON years.年度 = prod_data.年度 AND bad_data.月份 = prod_data.月份 GROUP BY years.年度 ) -- 最终结果集 SELECT 年月, 报废原因, 不良数量, 生产台数, CASE WHEN 生产台数 > 0 THEN ROUND((不良数量 * 100.0) / 生产台数, 4) ELSE 0 END AS 不良率 FROM ( SELECT * FROM MonthlyData2025 UNION ALL SELECT * FROM YearlyAvgData ) result ORDER BY CASE WHEN 年月 LIKE '%平均' THEN 0 ELSE 1 END, 年月 DESC; 以上语句增加2025年月度数据
07-23
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值