Oracle trunc

Written on 27 January 2014, 04:40pm

Tagged with: , , ,

A quick note about what the Oracle TRUNC function can do:
If you have a table ORDERS with a date field DATE_CREATION and you want to filter the records with a given date, you can use the TRUNC function.


SELECT ID 
FROM ORDERS
WHERE TRUNC(ORDERS.DATE_CREATION) = TO_DATE('27/01/2014', 'DD/MM/YYYY')

This will truncate the time from the DATE_CREATION and leave out only the date.
The full syntax for TRUNC is:

TRUNC ( date, [ format ] )
date is the date to truncate.
format is the unit of measure to apply for truncating. If the format parameter is omitted, the TRUNC function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.
techonthenet.com

Update, 28/feb/2014: Another Oracle goodie – how to find out details about sequences:


select sequence_owner, sequence_name, min_value, max_value, last_number 
from all_sequences 
order by sequence_owner, sequence_name;

https://community.oracle.com/thread/2340939

29 February ColdFusion bug

Written on 9 March 2012, 10:57pm

Tagged with: , ,

This is just to log a funny bug I encountered a few days ago, on the 29th of February 2012. There was a piece of code that was comparing if a certain date is older than a year or not.
The algorithm to compare the date was creating the ‘one-year-ago date’ by substracting one unit from the current year. Something like:

<cfset aYearAgo = CreateDate(year-1,month,day)>

Obviously, this algorithm was failing on the 29th of February on the leap years (like 2012).
The solution is quite simple: instead of substracting one unit from the year, substract 365 days from the current date, using Coldfusion’s dateAdd function:

<cfset aYearAgo = DateAdd('d', -365, Now())> 
Image: Smashing Magazine’s Desktop Wallpaper Calendar February 2012