英文:
SQL Filter days equal to or higher than the 25th of the month
问题
报告月份 计划销售日期
202212 20230125
202212 20230130
202212 20230129
202212 20230129
英文:
I have two columns: reporting_month and scheduled_sales_date. On the scheduled_sales_date, I want to filter and show only dates with days equal to or higher than 25 of the month.
Default table :
reporting_month scheduled_sales_date
202212 20230117
202212 20230106
202212 20230125
202212 20230130
202212 20230101
202212 20230129
202212 20230114
202212 20230122
202212 20230129
Expected filtered table:
reporting_month scheduled_sales_date
202212 20230125
202212 20230130
202212 20230129
202212 20230129
答案1
得分: 2
你还没有告诉我们scheduled_sales_date是什么数据类型。当然，你应该将日期存储为日期，而不是数字或字符串。但我们不能确定。
对于日期'2023-01-17'，请使用：
where extract(day from scheduled_sales_date) >= 25
对于数字20230117，请使用：
where mod(scheduled_sales_date, 10) >= 25
对于字符串'20230117'，请使用：
where substring(scheduled_sales_date, 7, 2) >= '25'
英文:
You haven't told us what data type scheduled_sales_date is. You should store dates as dates, of course, not as numbers or strings. But we can't be sure.
for a date '2023-01-17' use:
where extract(day from scheduled_sales_date) >= 25
For a numeric 20230117 use:
where mod(scheduled_sales_date, 10) >= 25
for a string '20230117' use:
where substring(scheduled_sales_date, 7, 2) >= '25'
答案2
得分: 0
你没有提到scheduled_sales_date是一个数字字段还是日期字段，如果它是一个日期字段，那么TO_INTEGER(DAYOFMONTH(scheduled_sales_date)) >= 25应该起作用，否则如果它是一个数字字段，你可以使用scheduled_sales_date >= (year*10000+month*100+25)。
英文:
You have not mentioned whether the scheduled_sales_date is a number field or a date field if it is a date field then
TO_INTEGER(DAYOFMONTH(scheduled_sales_date)) >= 25 should work
else if it is a number field then you can use
scheduled_sales_date >=(year*10000+month*100+25)
答案3
得分: 0
SELECT reporting_month, scheduled_sales_date
FROM your_table_name
WHERE DAYOFMONTH(scheduled_sales_date) >= 25;
英文:
SELECT reporting_month, scheduled_sales_date
FROM your_table_name
WHERE DAYOFMONTH(scheduled_sales_date) >= 25;
reporting_month | scheduled_sales_date
-------------------------------------
202212 | 20230125
202212 | 20230130
202212 | 20230129
202212 | 20230129
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论