Shawn_Moore

CA Tuesday Tip: Using Dates in Queries

Discussion created by Shawn_Moore Employee on Jan 5, 2011
Latest reply on Jan 21, 2011 by Alex_Feldstein
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 1/4/2011

Here are some useful tips for queries that involve dates. This is not exhaustive and there may be better ways to perform some of comparisons below.


1) To check against the current date use sysdate and getDate() and a numeric day offset. (Whole numbers represent full days)

Oracle: sysdate

i.e. select count(1) from prtimesheet where prmodtime > sysdate-30 --count the number of timesheets that were created in the last 30 days.

MS SQL: getDate()

i.e. select count(1) from prtimesheet where prmodtime > getDate()-30 --count the number of timesheets that were created in the last 30 days.


2) In Oracle use the TO_DATE() function to convert string literals to dates.

i.e.

select * from prtimeperiod where prstart between TO_DATE('2008-07-05','YYYY-MM-DD') and TO_DATE('2008-09-05','YYYY-MM-DD')


3) In Oracle Be careful when comparing dates that are formatted as strings as human readable formats will not compare properly. (It is better to use to date.)

select * from prtimeperiod where
to_char(PRSTART,'mm/dd/yyyy') > to_char(sysdate-14,'mm/dd/yyyy') -- show me all the time periods with a start date in the last 2 weeks.

* This query will produce unexpected results when run in the first 2 weeks of January. Since January is represented as 01 numerically and December is represented as 12.

i.e. in pseudocode

give me all fields from the prtimeperiod table where 01/01/2011 > 12/20/2010

-- Since 01/01/2011 is less than 12/20/2010 in terms of raw string comparisons, this query will return no results.

Either change the format to 'yyyy/mm/dd' or better yet, use the TO_DATE function for such string comparisons.

4) MS SQL Server can support literal date formats which are compared as dates and not strings.

i.e.

select * from prtimeperiod where prstart between '03-NOV-08' and '09-NOV-08'

Shawn Moore

Outcomes