I always have to refer back when every time I need to query dates.
Here are some useful SQL statements I normally have it with me.
Remember ColdFusion has current date as now() and Oracle has the wonderful build-in function sysdate.
Simple command like sysdate+1 will add one day to the current date and sysdate-10 will minus 10 days from the current date.
Format date on select (output): TO_CHAR(date, ‘format‘)
Format on input: TO_DATE(string, ‘format‘)
| MM | Numeric month (e.g. 07) |
| MON | Abbreviated month name (e.g. JUL) |
| MONTH | Full month name (e.g. JULY) |
| DD | Day of month (e.g. 14) |
| DY | Abbreviated name of day (e.g. FRI) |
| YYYY | 4-digit year (e.g. 1999) |
| YY | Last 2 digits of the year (e.g. 99) |
| RR | Like YY, but the two digits are “rounded” to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906 |
| AM (or PM) | Meridian indicator |
| HH | Hour of day (1-12) |
| HH24 | Hour of day (0-23) |
| MI | Minute (0-59) |
| SS | Second (0-59) |
Try:
1 2 3 | SELECT TO_CHAR(sysdate+100, 'DD-Mon-YYYY HH24:MI:SS Dy') AS "Current Time" FROM dual / |
And now the tricky part is to compare two dates. Standard comparison operators does work; such as =, !=, > and <.
To get a date count between two dates – use round function.
For this example, I am querying all ex boyfriends in the past 365 days.
1 2 3 4 | SELECT firstname, lastname FROM boyfriendList WHERE ROUND(sysdate-breakupDate) < 365 / |
To validate a date exist between two dates – use between function.
For this example, I am getting all ex boyfriends that has the breakupDate exists between two dates.
Note: in ColdFusion, the variable startDate and endDate is wrapped around the pound sign.
1 2 3 4 5 | SELECT firstname, lastname
FROM boyfriendList
WHERE TRUNC(breakupDate) BETWEEN
TO_DATE('#startDate#','mm/dd/yyyy') AND TO_DATE('#endDate#','mm/dd/yyyy')
/ |
OR
1 2 3 4 5 | SELECT firstname, lastname
FROM boyfriendList
WHERE TRUNC (breakupDate) BETWEEN
TO_DATE(sysdate-31) AND TO_DATE(sysdate-1)
/ |
Now, what is the difference between TRUNC and ROUND?
ROUND will always round the number to the closest integer.
For example:
1 2 3 4 5 | SELECT
ROUND(33.99) AS round_number,
TRUNC(33.99) AS trunc_number
FROM DUAL
/ |
will return result of 34 for round_number and 33 for trunc_number.
