Choosing dates in SQL

If there is a datetime field in your database, chances is that you would want to choose a period between dates. There are more than one way to choose a period.

First I would show the wrong way.

Select mydate from mytable where datefield > '1/1/1900' and datefield < '1/31/1900'

This SQL is not wrong by itself. However, without time qualification, the default time is 00:00:00. It then posts a problem. ">" actually means greater than. This means that the date 1/1/1900 00:00:00 is excluded. "<" refers to less than. This means that the date 1/31/1900 00:00:00 and above will not be included too. In English, this condition means "exclusive" term.

A more correct way is as follows.

select my date from mytable where datefield >= '1/1/1900' and datefield < '2/1/1900'

Another way to select a period is as follows.

select my date from mytable where datefield between '1/1/1900' and '1/31/1900'

I am not sure if that is correct too since the time definition on the second date is missing. To be more exact the second date should be 1/31/1900 23:59:59.