Python连接Oracle数据库，使用SQL将整数字段转换为日期失败的问题求助

Hey there! Let's break down why your current query isn't working and how to fix it. The issue here is that your DATECREATED field is an integer (like 20240520 for May 20, 2024), but Oracle's TO_DATE() function expects a string as its first argument. While Oracle does try implicit conversions, they're unreliable for this scenario—so we need to explicitly turn that integer into a string first.

Solution 1: Explicitly Convert Integer to String #

Modify your SQL to first cast the integer to a character string using TO_CHAR(), then pass that to TO_DATE() with your format mask:

connection = cx_Oracle.connect(user, pwd, dsn, encoding="UTF-8")
cursor = connection.cursor()
# Updated query with explicit string conversion
results = cursor.execute("""
    SELECT TO_DATE(TO_CHAR(DATECREATED), 'YYYYMMDD') AS DATECREATED
    FROM ARADMIN.EPM_TechnicianInformation
""").fetchall()

This ensures the integer is properly converted to a string that matches the YYYYMMDD format TO_DATE() expects.

Solution 2: Handle Short Integer Values (Optional) #

If some of your DATECREATED values are shorter than 8 digits (e.g., 2024520 instead of 20240520), use LPAD() to pad leading zeros and maintain the 8-digit format:

results = cursor.execute("""
    SELECT TO_DATE(LPAD(TO_CHAR(DATECREATED), 8, '0'), 'YYYYMMDD') AS DATECREATED
    FROM ARADMIN.EPM_TechnicianInformation
""").fetchall()

LPAD(TO_CHAR(DATECREATED), 8, '0') will ensure every value is 8 characters long, filling in leading zeros where needed before converting to a date.

Why Your Original Query Failed #

When you pass an integer directly to TO_DATE(), Oracle attempts to convert it to a string automatically, but this can fail if the integer's length doesn't align with the format mask, or if Oracle's implicit conversion rules don't match your expected output. Explicitly converting to a string removes this ambiguity.

Once you run the corrected query, cx_Oracle will map the Oracle date type to Python's datetime object, making it easy to work with the dates in your code.

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