Clarity PPM

SQL Tip: TRUNC vs TO_CHAR 

11-28-2017 11:08 AM

Sometimes you need to compare dates in SQL but you don't want the hours and minutes to count. 

 

2017-01-01 08:30:11

 

I have seen quite some SQL code using the TO_CHAR function: TO_CHAR(date, 'yyyy-mm-dd'). But this is quite a performance expansive function. Especially if used a lot in the query.

 

You can use another (much more performant function) : TRUNC

 

TRUNC(2017-01-01 08:30:11) will give: 2017-01-01 00:00:00 as a result and will allow you to compare dates independently from the hours and minutes as well.

 

Hope this helps,

Statistics
0 Favorited
1 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

12-13-2017 06:04 PM

Anthony,

 

Good call, but it's also important that both sides of the comparison are DATE data types. Any time there is type conversion performance is impacted. It's also important when performing queries that the data types match the field types in order to have the execution plan hit the indices that are behind the queries.

 

Hope this adds context.

12-01-2017 03:06 PM

Thank you for sharing this tip with the community Anthony!

SQL Tip: TRUNC vs TO_CHAR 

Related Entries and Links

No Related Resource entered.