如何在Python的Pandasql WHERE子句中使用当前日期？

Got it, let's break down what's happening here and fix it quickly.

The error you're seeing happens because getdate() is a SQL Server-specific function, but pandasql uses SQLite under the hood. SQLite doesn't recognize getdate()—it has its own set of date/time functions we need to use instead. Here are two reliable ways to filter for today and future records:

1. Use SQLite's Built-in Date Functions #

SQLite has date('now') to get the current date (without time) and datetime('now') to get the current date and time. Pick the one that matches the type of your Date column:

If your Date column is a date-only type (no time): #

q = """ 
select e.Date, e.SITA, e.Events 
from df_event e 
where e.Date >= date('now') 
"""

If your Date column includes time (datetime type): #

If you want to include all records from the start of today onward, use datetime('now') truncated to midnight, or compare the date part of your column:

# Option A: Compare date parts to ignore time
q = """ 
select e.Date, e.SITA, e.Events 
from df_event e 
where date(e.Date) >= date('now') 
"""

# Option B: Use current datetime (includes time for precise filtering)
q = """ 
select e.Date, e.SITA, e.Events 
from df_event e 
where e.Date >= datetime('now') 
"""

2. Pass a Python Date Variable (More Flexible) #

For better control (like handling timezones or specific date ranges), fetch the current date in Python and pass it as a parameter to your query. This avoids relying on SQLite's date functions entirely:

from datetime import date, datetime
import pandasql as ps

# Get today's date (date-only)
today = date.today()

# Or get the start of today (datetime with time set to 00:00:00)
today_start = datetime.today().replace(hour=0, minute=0, second=0, microsecond=0)

# Use the variable in your query with a placeholder `?`
q = """ 
select e.Date, e.SITA, e.Events 
from df_event e 
where e.Date >= ? 
"""

# Execute with the parameter
result = ps.sqldf(q, params=[today])  # or [today_start] if using datetime

Critical Pre-check #

Make sure your df_event['Date'] column is a proper date/datetime type in pandas, not a string. If it's stored as a string, convert it first:

import pandas as pd
df_event['Date'] = pd.to_datetime(df_event['Date'])

This ensures the date comparisons work as expected—SQLite can't properly compare string dates to actual date values.

内容的提问来源于stack exchange，提问作者Sorath