Date queries in general can be difficult to handle in SQL.  Not only do you have to think about the syntax for both SQL Server and Oracle but to do “simple” queries like “get all approved work orders this week” isn’t that easy.  Hopefully these will help you in creating advanced date queries to full fill all your needs for both Microsoft SQL Server and Oracle.  These can also be used in workflows as well.

For Microsoft SQL Server:

Users often want to query records based on fixed date ranges, such as “Last 7 days” or “This week” without having to enter dates every time. This shows SQL syntax that can be added to any where clause to do this dynamically.

For Microsoft SQL Server:

For records with a date and time within the last seven days of the current time. Changedatecolumn to the appropriate column for the table:

AND (datecolumn BETWEEN dateadd(day, -7, getdate()) AND getdate())

For records with a date within the last seven calendar days (the Convert function allows you to set the date to a mm/dd/yyyy string format, which cuts off the time component, effectively setting it to midnight):

AND (datecolumn BETWEEN dateadd(day, – 7, convert(varchar, getdate(), 101)) AND getdate())

Here are the same query conditions, but for a range of the last 30 days from the current date.

For records with a date and time within the last thirty days to the current time. Change datecolumnto the appropriate column for the table:

AND (datecolumn BETWEEN dateadd(day, -30, getdate()) AND getdate())

Records with a date within the last thirty calendar days.

AND (datecolumn BETWEEN dateadd(day, – 30, convert(varchar, getdate(), 101)) AND getdate())

You can also use this type of condition to select by a specific calendar week (Sun – Sat) instead of 7 days prior to today. The week definition is established by the command Set Datefirst. This establishes the day of the week that the week starts on. The Default is 7 for the U.S. English week starting on Sunday. The statement ‘datepart(dw, getdate())’ will now display a value of 1 for Sunday and 7 for Saturday.

Records with a datecolumn value that falls within the current week, where the week starts with Sunday:

AND (datepart(wk, datecolumn) = datepart(wk, getdate())) and (datepart(year, datecolumn) = datepart(year, getdate()))

Records with a datecolumnvalue that falls within the previous week, from Sunday through Saturday midnight:

AND ( datepart(wk, datecolumn) = datepart(wk, dateadd(wk, – 1, getdate()))) and (datepart(year,datecolumn) = datepart(year, getdate()))

A DBA or Maximo Administrator can develop useful date range queries to meet the user’s needs, and make them available as public saved queries in the various application query screens. You can then pick date queries and enhance them to add personal criteria, such as ENTEREDBY, SUPERVISOR, etc. and save them as your own. [IBM Support – Advanced date queries (SQL)]

For Oracle:

Users often want to query records based on fixed date ranges, such as “Last 7 days” or “This week” without having to enter dates every time. This shows SQL syntax that can be added to any where clause to do this dynamically.

For Oracle:

Records with a date within the last seven days to the second. Change datecolumn to the appropriate column for the table:

AND (datecolumn BETWEEN SYSDATE-7 AND SYSDATE)

Records with a date within the last seven calendar days (the TRUNC function cuts off the time component, effectively setting it to midnight):

AND (datecolumn BETWEEN TRUNC(SYSDATE-7) AND SYSDATE)

Changing the 7 to 30 in the above conditions to query for the last 30 days.

Records with a date within the last seven days to the second. Change datecolumn to the appropriate column for the table:

AND (datecolumn BETWEEN SYSDATE-30 AND SYSDATE)

Records with a date within the last seven calendar days (the TRUNC function cuts off the time component, effectively setting it to midnight):

AND (datecolumn BETWEEN TRUNC(SYSDATE-30) AND SYSDATE)

It gets a little tricky, but you can also use this type of condition to select by a specific calendar week (Sun – Sat) instead of 7 days prior to today:

Records with a datecolumn value that falls within the current week, where the week starts with Sunday:

AND (datecolumn BETWEEN (TRUNC(SYSDATE – (TO_CHAR(SYSDATE,’D’)-1))) AND SYSDATE)

Records with a datecolumnvalue that falls within the previous week, from Sunday through Saturday midnight:

AND (datecolumn BETWEEN (TRUNC(SYSDATE – (TO_CHAR(SYSDATE,’D’)+6))) AND (TRUNC(SYSDATE – (TO_CHAR(SYSDATE,’D’)-1))))

A DBA or MAXIMO Administrator can develop useful date range queries to meet the users’ needs, and make them available as public saved queries in the various applications’ query screens. Users can then pick date queries and enhance them to add personal criteria, such as ENTEREDBY, SUPERVISOR, etc. and save them as their own. [IBM Support – Advanced date queries – (Oracle)]

Here is a another suggestion from Dominic Russo (Thanks!):

I’d like to make one huge suggestion for SQL. Use DATEDIFF!!! It is much easier and does not require any DATEADDs or anything like that. It’s quite simple:

If you’re looking for data within the past 7 days (including today), use DATEDIFF( DAY, datecolumn, GETDATE() )<=7

If you didn’t want to include today use:
DATEDIFF( DAY, datecolumn, GETDATE() ) BETWEEN 1 AND 7

If you wanted all records for last week, you can use:
DATEDIFF( WEEK, datecolumn, GETDATE() ) = 1
(please note that weeks run Sun through Sat by default)

If you wanted all records for last week, but with the week running Mon thought Sunday (instead of the default Sun through Sat), then use:
DATEDIIFF( WEEK, datecolumn-1, GETDATE()-1 ) = 1

If you’re looking into the future (perhaps for some scheduling type app) you can just flip the 2nd and 3rd parameters of the DATEDIFF!

If you want all records for 2 weeks in the future use:
DATEDIFF( WEEK, GETDATE(), datecolumn) = 2

If you wanted all records in the next 30 days use:
DATEDIFF( DAY, GETDATE(), datecolumn) <=30

I think DATEDIFF is by far the easiest way to handle SQL date ranges!