{"id":4221,"date":"2024-10-03T11:32:44","date_gmt":"2024-10-03T03:32:44","guid":{"rendered":"https:\/\/five.co\/?p=4221"},"modified":"2026-05-07T08:21:44","modified_gmt":"2026-05-07T00:21:44","slug":"sql-date-filtering","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/sql-date-filtering\/","title":{"rendered":"SQL Date Filtering: Practical Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Breakdown of SQL Date Filtering<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Date filtering in SQL is a crucial skill for data analysis and management. Whether you&#8217;re tracking sales trends, analyzing user behavior, or maintaining audit logs, using date-based queries will significantly improve your data manipulation capabilities. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s dive into various techniques for filtering data by date in SQL, complete with practical examples and real-world scenarios.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-2ax3w wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-2ax3w \"><div class=\"eb-toc-container eb-toc-2ax3w  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;Breakdown of SQL Date Filtering&quot;,&quot;text&quot;:&quot;Breakdown of SQL Date Filtering&quot;,&quot;link&quot;:&quot;breakdown-of-sql-date-filtering&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Basic Date Filtering&quot;,&quot;text&quot;:&quot;Basic Date Filtering&quot;,&quot;link&quot;:&quot;basic-date-filtering&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Filtering by Year, Month, or Day&quot;,&quot;text&quot;:&quot;Filtering by Year, Month, or Day&quot;,&quot;link&quot;:&quot;filtering-by-year-month-or-day&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Date Arithmetic for Dynamic Filtering&quot;,&quot;text&quot;:&quot;Date Arithmetic for Dynamic Filtering&quot;,&quot;link&quot;:&quot;date-arithmetic-for-dynamic-filtering&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Handling Different Date Formats&quot;,&quot;text&quot;:&quot;Handling Different Date Formats&quot;,&quot;link&quot;:&quot;handling-different-date-formats&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Real-World Scenario: Sales Analysis Dashboard&quot;,&quot;text&quot;:&quot;Real-World Scenario: Sales Analysis Dashboard&quot;,&quot;link&quot;:&quot;real-world-scenario-sales-analysis-dashboard&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Optimizing Date-Based Queries&quot;,&quot;text&quot;:&quot;Optimizing Date-Based Queries&quot;,&quot;link&quot;:&quot;optimizing-date-based-queries&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Handling Time Zones&quot;,&quot;text&quot;:&quot;Handling Time Zones&quot;,&quot;link&quot;:&quot;handling-time-zones&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Advanced Date Filtering: Working with Fiscal Years&quot;,&quot;text&quot;:&quot;Advanced Date Filtering: Working with Fiscal Years&quot;,&quot;link&quot;:&quot;advanced-date-filtering-working-with-fiscal-years&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Handling Missing or Invalid Dates&quot;,&quot;text&quot;:&quot;Handling Missing or Invalid Dates&quot;,&quot;link&quot;:&quot;handling-missing-or-invalid-dates&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Conclusion: Using SQL Date Filtering&quot;,&quot;text&quot;:&quot;Conclusion: Using SQL Date Filtering&quot;,&quot;link&quot;:&quot;conclusion-using-sql-date-filtering&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;Breakdown of SQL Date Filtering&quot;,&quot;value&quot;:&quot;breakdown-of-sql-date-filtering&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Basic Date Filtering&quot;,&quot;value&quot;:&quot;basic-date-filtering&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Filtering by Year, Month, or Day&quot;,&quot;value&quot;:&quot;filtering-by-year-month-or-day&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Date Arithmetic for Dynamic Filtering&quot;,&quot;value&quot;:&quot;date-arithmetic-for-dynamic-filtering&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Handling Different Date Formats&quot;,&quot;value&quot;:&quot;handling-different-date-formats&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Real-World Scenario: Sales Analysis Dashboard&quot;,&quot;value&quot;:&quot;real-world-scenario-sales-analysis-dashboard&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Optimizing Date-Based Queries&quot;,&quot;value&quot;:&quot;optimizing-date-based-queries&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Handling Time Zones&quot;,&quot;value&quot;:&quot;handling-time-zones&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Advanced Date Filtering: Working with Fiscal Years&quot;,&quot;value&quot;:&quot;advanced-date-filtering-working-with-fiscal-years&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Handling Missing or Invalid Dates&quot;,&quot;value&quot;:&quot;handling-missing-or-invalid-dates&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Conclusion: Using SQL Date Filtering&quot;,&quot;value&quot;:&quot;conclusion-using-sql-date-filtering&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#basic-date-filtering\">Basic Date Filtering<\/a><li><a href=\"#filtering-by-year-month-or-day\">Filtering by Year, Month, or Day<\/a><li><a href=\"#date-arithmetic-for-dynamic-filtering\">Date Arithmetic for Dynamic Filtering<\/a><li><a href=\"#handling-different-date-formats\">Handling Different Date Formats<\/a><li><a href=\"#real-world-scenario-sales-analysis-dashboard\">Real-World Scenario: Sales Analysis Dashboard<\/a><li><a href=\"#optimizing-date-based-queries\">Optimizing Date-Based Queries<\/a><li><a href=\"#handling-time-zones\">Handling Time Zones<\/a><li><a href=\"#advanced-date-filtering-working-with-fiscal-years\">Advanced Date Filtering: Working with Fiscal Years<\/a><li><a href=\"#handling-missing-or-invalid-dates\">Handling Missing or Invalid Dates<\/a><li><a href=\"#conclusion-using-sql-date-filtering\">Conclusion: Using SQL Date Filtering<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Basic Date Filtering<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s start with the fundamentals. Suppose we have a <code>sales<\/code> table with a <code>sale_date<\/code> column. Here&#8217;s how to filter sales for a specific date:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT * FROM sales\nWHERE sale_date = '2023-05-15';<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-05-15&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query retrieves all sales records for May 15, 2023. Simple, right? But what if we want to find sales within a date range?<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>SELECT * FROM sales\nWHERE sale_date BETWEEN '2023-05-01' AND '2023-05-31';<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #C678DD\">BETWEEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-05-01&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-05-31&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query fetches all sales for May 2023. The <code>BETWEEN<\/code> operator is inclusive, meaning it includes the start and end dates.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Filtering by Year, Month, or Day<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Often, you&#8217;ll need to filter data for a specific year, month, or day, regardless of the full date. Most SQL databases provide functions to extract these components:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Filter by year\nSELECT * FROM sales\nWHERE EXTRACT(YEAR FROM sale_date) = 2023;\n\n-- Filter by month\nSELECT * FROM sales\nWHERE EXTRACT(MONTH FROM sale_date) = 5;\n\n-- Filter by day of the month\nSELECT * FROM sales\nWHERE EXTRACT(DAY FROM sale_date) = 15;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Filter by year<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2023<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Filter by month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">MONTH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">5<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Filter by day of the month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">DAY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">15<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">These queries filter sales for the year 2023, the month of May (across all years), and the 15th day of any month, respectively.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Date Arithmetic for Dynamic Filtering<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Sometimes you need to filter based on relative dates. SQL allows date arithmetic for such cases:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Sales in the last 30 days\nSELECT * FROM sales\nWHERE sale_date >= CURRENT_DATE - INTERVAL '30 days';\n\n-- Sales in the current month\nSELECT * FROM sales\nWHERE sale_date >= DATE_TRUNC('month', CURRENT_DATE);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Sales in the last 30 days<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;30 days&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Sales in the current month<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, CURRENT_DATE);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The first query retrieves sales from the last 30 days, while the second gets sales for the current month. Note that <code>DATE_TRUNC<\/code> rounds down to the first day of the month.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Handling Different Date Formats<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Date formats can vary across databases and data sources. Here&#8217;s how to handle some common scenarios:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Convert string to date\nSELECT * FROM sales\nWHERE sale_date = TO_DATE('15\/05\/2023', 'DD\/MM\/YYYY');\n\n-- Filter using a different date format\nSELECT * FROM sales\nWHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '2023-05-15';<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Convert string to date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> TO_DATE(<\/span><span style=\"color: #98C379\">&#39;15\/05\/2023&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;DD\/MM\/YYYY&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Filter using a different date format<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> TO_CHAR(sale_date, <\/span><span style=\"color: #98C379\">&#39;YYYY-MM-DD&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-05-15&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>TO_DATE<\/code> function converts a string to a date, while <code>TO_CHAR<\/code> converts a date to a string in a specified format. These are particularly useful when working with user inputs or inconsistent date formats.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Real-World Scenario: Sales Analysis Dashboard<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Let&#8217;s apply these techniques to a real-world scenario. Imagine you&#8217;re building a sales analysis dashboard for an e-commerce platform. Here are some queries you might use:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Daily sales for the last week\nSELECT sale_date, SUM(amount) as daily_total\nFROM sales\nWHERE sale_date >= CURRENT_DATE - INTERVAL '7 days'\nGROUP BY sale_date\nORDER BY sale_date;\n\n-- Monthly sales trend\nSELECT \n    DATE_TRUNC('month', sale_date) as month,\n    SUM(amount) as monthly_total\nFROM sales\nWHERE sale_date >= DATE_TRUNC('year', CURRENT_DATE)\nGROUP BY DATE_TRUNC('month', sale_date)\nORDER BY month;\n\n-- Year-over-year comparison\nSELECT \n    EXTRACT(MONTH FROM sale_date) as month,\n    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = EXTRACT(YEAR FROM CURRENT_DATE) THEN amount ELSE 0 END) as current_year,\n    SUM(CASE WHEN EXTRACT(YEAR FROM sale_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN amount ELSE 0 END) as previous_year\nFROM sales\nWHERE sale_date >= DATE_TRUNC('year', CURRENT_DATE - INTERVAL '1 year')\nGROUP BY EXTRACT(MONTH FROM sale_date)\nORDER BY month;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Daily sales for the last week<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> sale_date, <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(amount) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> daily_total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;7 days&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> sale_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> sale_date;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Monthly sales trend<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, sale_date) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(amount) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> monthly_total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;year&#39;<\/span><span style=\"color: #ABB2BF\">, CURRENT_DATE)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;month&#39;<\/span><span style=\"color: #ABB2BF\">, sale_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Year-over-year comparison<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    EXTRACT(<\/span><span style=\"color: #C678DD\">MONTH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE) <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> amount <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> current_year,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE) - <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> amount <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> previous_year<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> DATE_TRUNC(<\/span><span style=\"color: #98C379\">&#39;year&#39;<\/span><span style=\"color: #ABB2BF\">, CURRENT_DATE - INTERVAL <\/span><span style=\"color: #98C379\">&#39;1 year&#39;<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">MONTH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">month<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">These queries provide daily sales for the last week, a monthly sales trend for the current year, and a year-over-year comparison. They form the backbone of a comprehensive sales analysis dashboard.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Optimizing Date-Based Queries<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When working with large datasets, performance becomes crucial. Here are some tips to optimize your date-based queries:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Use indexes<\/strong>: Ensure your date columns are properly indexed.<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE INDEX idx_sale_date ON sales(sale_date);<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">INDEX<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">idx_sale_date<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ON<\/span><span style=\"color: #ABB2BF\"> sales(sale_date);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li><strong>Avoid functions on indexed columns<\/strong>: Instead of <code>EXTRACT(YEAR FROM sale_date) = 2023<\/code>, use:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>WHERE sale_date >= '2023-01-01' AND sale_date &lt; '2024-01-01'<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-01-01&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AND<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&lt;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2024-01-01&#39;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li><strong>Partitioning<\/strong>: For very large tables, consider partitioning by date range:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>CREATE TABLE sales (\n    id INT,\n    sale_date DATE,\n    amount DECIMAL(10,2)\n) PARTITION BY RANGE (sale_date);\n\nCREATE TABLE sales_2023 PARTITION OF sales\n    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">sales<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    id <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    sale_date <\/span><span style=\"color: #C678DD\">DATE<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    amount <\/span><span style=\"color: #C678DD\">DECIMAL<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">10<\/span><span style=\"color: #ABB2BF\">,<\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">BY<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">RANGE<\/span><span style=\"color: #ABB2BF\"> (sale_date);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #61AFEF\">sales_2023<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PARTITION<\/span><span style=\"color: #ABB2BF\"> OF sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FOR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;2023-01-01&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">TO<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #98C379\">&#39;2024-01-01&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This allows the database to quickly eliminate irrelevant partitions during query execution.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Handling Time Zones<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">When dealing with global data, time zones can complicate date filtering. Here&#8217;s how to handle them:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Convert local time to UTC\nSELECT * FROM sales\nWHERE sale_date AT TIME ZONE 'America\/New_York' >= '2023-05-15 00:00:00'::TIMESTAMP AT TIME ZONE 'UTC';\n\n-- Filter by local time in different time zones\nSELECT * FROM sales\nWHERE sale_date AT TIME ZONE 'UTC' AT TIME ZONE 'America\/Los_Angeles' >= '2023-05-15 00:00:00';<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Convert local time to UTC<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;America\/New_York&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-05-15 00:00:00&#39;<\/span><span style=\"color: #ABB2BF\">::<\/span><span style=\"color: #C678DD\">TIMESTAMP<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;UTC&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Filter by local time in different time zones<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;UTC&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">TIME<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">ZONE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;America\/Los_Angeles&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;2023-05-15 00:00:00&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">These queries ensure consistent date filtering regardless of the time zone of the stored data or the user&#8217;s location.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Advanced Date Filtering: Working with Fiscal Years<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Many organizations operate on fiscal years that don&#8217;t align with calendar years. Here&#8217;s how to handle such scenarios:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Assuming fiscal year starts on April 1\nWITH fiscal_sales AS (\n    SELECT *,\n           CASE \n               WHEN EXTRACT(MONTH FROM sale_date) >= 4 THEN EXTRACT(YEAR FROM sale_date)\n               ELSE EXTRACT(YEAR FROM sale_date) - 1\n           END AS fiscal_year\n    FROM sales\n)\nSELECT fiscal_year, SUM(amount) as fiscal_year_total\nFROM fiscal_sales\nGROUP BY fiscal_year\nORDER BY fiscal_year;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Assuming fiscal year starts on April 1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> fiscal_sales <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> *,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">           <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">               <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">MONTH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) <\/span><span style=\"color: #56B6C2\">&gt;=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">4<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">               <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> EXTRACT(<\/span><span style=\"color: #C678DD\">YEAR<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sale_date) - <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">           <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> fiscal_year<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> fiscal_year, <\/span><span style=\"color: #56B6C2\">SUM<\/span><span style=\"color: #ABB2BF\">(amount) <\/span><span style=\"color: #C678DD\">as<\/span><span style=\"color: #ABB2BF\"> fiscal_year_total<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> fiscal_sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">GROUP BY<\/span><span style=\"color: #ABB2BF\"> fiscal_year<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">ORDER BY<\/span><span style=\"color: #ABB2BF\"> fiscal_year;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query calculates total sales by fiscal year, assuming the fiscal year starts on April 1.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Handling Missing or Invalid Dates<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">In real-world datasets, you might encounter missing or invalid dates. Here&#8217;s how to deal with them:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><pre class=\"code-block-pro-copy-button-pre\" aria-hidden=\"true\"><textarea class=\"code-block-pro-copy-button-textarea\" tabindex=\"-1\" aria-hidden=\"true\" readonly>-- Exclude null dates\nSELECT * FROM sales\nWHERE sale_date IS NOT NULL;\n\n-- Check for invalid dates\nSELECT * FROM sales\nWHERE sale_date IS NULL OR sale_date > CURRENT_DATE;\n\n-- Replace invalid dates with a default value\nSELECT \n    CASE \n        WHEN sale_date IS NULL OR sale_date > CURRENT_DATE THEN '1900-01-01'::DATE\n        ELSE sale_date\n    END AS cleaned_sale_date,\n    amount\nFROM sales;<\/textarea><\/pre><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Exclude null dates<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #C678DD\">IS NOT NULL<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Check for invalid dates<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">OR<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #7F848E; font-style: italic\">-- Replace invalid dates with a default value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">CASE<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">WHEN<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #C678DD\">IS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">NULL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">OR<\/span><span style=\"color: #ABB2BF\"> sale_date <\/span><span style=\"color: #56B6C2\">&gt;<\/span><span style=\"color: #ABB2BF\"> CURRENT_DATE <\/span><span style=\"color: #C678DD\">THEN<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;1900-01-01&#39;<\/span><span style=\"color: #ABB2BF\">::<\/span><span style=\"color: #C678DD\">DATE<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">ELSE<\/span><span style=\"color: #ABB2BF\"> sale_date<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> cleaned_sale_date,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    amount<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> sales;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">These queries help identify and handle problematic date values in your dataset.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion: Using SQL Date Filtering<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">SQL date filtering is a fundamental skill that opens up a world of possibilities for data analysis and management. We&#8217;ve covered:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Basic date filtering techniques<\/li>\n\n\n\n<li>Extracting year, month, and day components<\/li>\n\n\n\n<li>Dynamic date filtering using date arithmetic<\/li>\n\n\n\n<li>Handling different date formats<\/li>\n\n\n\n<li>Real-world application in a sales analysis scenario<\/li>\n\n\n\n<li>Query optimization techniques<\/li>\n\n\n\n<li>Dealing with time zones<\/li>\n\n\n\n<li>Advanced scenarios like fiscal year calculations<\/li>\n\n\n\n<li>Handling missing or invalid dates<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">Remember, the most effective date filtering approach depends on your specific use case, data structure, and performance requirements. As you apply these techniques, you&#8217;ll discover more nuanced ways to analyze and manipulate date-based data in SQL.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access to Five\u2019s online development environment and start building your MySQL web application today.<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Breakdown of SQL Date Filtering Date filtering in SQL is a crucial skill for data analysis and management. Whether you&#8217;re tracking sales trends, analyzing user behavior, or maintaining audit logs, using date-based queries will significantly improve your data manipulation capabilities. Let&#8217;s dive into various techniques for filtering data by date in SQL, complete with practical [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":4222,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-4221","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Date-Filtering-Practical-Guide.jpg",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Date-Filtering-Practical-Guide-150x150.jpg",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Date-Filtering-Practical-Guide-300x169.jpg",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Date-Filtering-Practical-Guide-768x432.jpg",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Date-Filtering-Practical-Guide-1024x576.jpg",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Date-Filtering-Practical-Guide.jpg",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/10\/Five.Co-SQL-Date-Filtering-Practical-Guide.jpg",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"Breakdown of SQL Date Filtering Date filtering in SQL is a crucial skill for data analysis and management. Whether you&#8217;re tracking sales trends, analyzing user behavior, or maintaining audit logs, using date-based queries will significantly improve your data manipulation capabilities. Let&#8217;s dive into various techniques for filtering data by date in SQL, complete with practical&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4221","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=4221"}],"version-history":[{"count":1,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4221\/revisions"}],"predecessor-version":[{"id":17823,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/4221\/revisions\/17823"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/4222"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=4221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=4221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=4221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}