SQL中字符串转指定日期格式：将'05/18/2016 08:57'转为日期类型

Let's sort out this date logic—your current query has unnecessary nested functions and a format string mistake that's causing issues. Here's how to clean it up properly:

First, Identify the Problems in Your Original Query #

Your original code has redundant to_char() calls wrapped around the date strings, plus a typo in the format mask (that extra comma in 'mm/dd/yyyy,hh24:mi:ss' will break the conversion). You don't need to convert a string to a string again before turning it into a date!

Correct Approach for Date Conversion & Range Check #

We'll simplify the logic based on whether datefield1 is a date type or a string type in your database:

Case 1: datefield1 is already a DATE column

If datefield1 is stored as a native date type, you don't need to wrap it in to_date() at all. Just truncate it directly:

TRUNC(datefield1) BETWEEN 
  TRUNC(TO_DATE('05/18/2016 08:57', 'mm/dd/yyyy hh24:mi:ss')) 
  AND 
  TRUNC(TO_DATE('05/20/2016 08:57', 'mm/dd/yyyy hh24:mi:ss'));

Case 2: datefield1 is stored as a STRING (VARCHAR)

If datefield1 is a string, first convert it to a date with the matching format mask, then truncate:

TRUNC(TO_DATE(datefield1, 'mm/dd/yyyy hh24:mi:ss')) BETWEEN 
  TRUNC(TO_DATE('05/18/2016 08:57', 'mm/dd/yyyy hh24:mi:ss')) 
  AND 
  TRUNC(TO_DATE('05/20/2016 08:57', 'mm/dd/yyyy hh24:mi:ss'));

Key Optimizations & Notes #

• Remove redundant to_char(): You can directly pass the date string to TO_DATE() with the correct format mask—no need to convert it to a string first.
• Fix the format mask: The mask must exactly match the structure of your input string. Your original mask had an extra comma (yyyy,hh24) which would throw a conversion error.
• TRUNC() behavior: When you truncate a date without specifying a parameter, it defaults to truncating to the start of the day (midnight), which is exactly what you want for a date-only range check.

Bonus: Even Simpler Range Check #

If you only care about the date part (not the time), you could also rewrite the condition to avoid TRUNC() entirely (which can help with index usage if you have an index on datefield1):

datefield1 >= TO_DATE('05/18/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss')
AND datefield1 < TO_DATE('05/21/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss');

This checks for all times on 05/18, 05/19, and up to (but not including) midnight on 05/21—same result as your original truncate logic, but more index-friendly.

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