Sql Shorthand For Dates

Question

Is there a way to write a query equivalent to

select * from log_table where dt >= 'nov-27-2009' and dt < 'nov-28-2009';

but where you could specify only 1 date and say you want the results for that entire day until the next one.

I'm just making this up, but something of the form:

select * from log_table where dt = 'nov-27-2009':+1;

Answer 1

For MS SQL Server, check out DATEPART.

/* dy = Day of Year */
select * from log_table where datepart(dy, dt) = datepart(dy, '2009-nov-27');

Answer 2

I do not believe there is one method that is portable to all RDBMSes.

A check in one of my references (SQL Cookbook) shows that no one RDBMS solves the problem quite the same way. I would recommend checking out Chapter 8 of that book, which covers all of the different methods for DB2, Oracle, PostgreSQL, MySQL.

I've had to deal with this issue in SQLite, though, and SQL Cookbook doesn't address that RDBMS, so I'll mention a bit about it here. SQLite doesn't have a date/time data type; you have to create your own by storing all date/time data as TEXT and ensure that your application enforces its formatting. SQLite does have a set of date/time conversion functions that allow you to add nominal date/times while maintaining the data as strings. If you need to add two time durations (HH:MM:SS) to each other, though, based upon data that you've stored in text columns that you are treating as date/time data, you'll have to write your own functions (search for "Defining SQLite User Functions") and attach them to the database at runtime via a call to sqlite3_create_function(). If you want an example of some user functions that add time values, let me know.

Answer 3

With SQL Server, you could

   Select * From table
   Where dt >= DateAdd(day, DateDiff(day, 0, @ParamDate), 0)
      And dt < DateAdd(day, DateDiff(day, 0, @ParamDate), 1)

Answer 4

As long as you are dealing with the date data type for the respective data type, the following will work:

t.date_column + 1

...will add one day to the given date. But I have yet to find a db that allows for implicit data type conversion into a date.

SELECT '12-10-2009' + 1

...will fail on SQL Server because SQL Server only performs the implicit conversion when comparing to a datetime data type column. So you need to use:

SELECT CONVERT(DATETIME, '12-10-2009') + 1

For Oracle, you'd have to use the TO_DATE function; MySQL would use something like STR_TO_DATE, etc.

Answer 5

Have a column that just has the date part (time is 00:00:00.000) and then you can add a where clause: WHERE dt = '2009-11-27'