DATE and TIME ... don't be scared, and do it right (the first time)

I presume you all have already worked with databases and queries, and found some difficulties to get the date/time stuff right. Though it's not rocket science, the purpose here is to teach you how to handle date and times, and to avoid implicit data type conversions.
NOTE: I refer to the versions MSSQL Server 7 through 2008, MySQL 4.x through 5.x and higher, and Oracle 8.x through 11.x, unless specified otherwise. Most of the information is version independent, though.

Be sure to know the data type when you declare your variables and table columns!

In MS SQL Server, the data type is named DATETIME, containing both date and time.
 => SQL Server 2008 comes with a range of new data types for the date and time, see also Step 2
MySQL has DATE, DATETIME and TIMESTAMP (datetime + offset).
Oracle has DATE and TIMESTAMP (datetime + offset).

Don't get fooled by MS SQL Server: it does have a data type called TIMESTAMP, but it is NOT the same as Oracle's timestamp, and actually, it is not a date or time at all. In short: remember to forget that data type in SQL server: http://msdn.microsoft.com/en-us/library/ms182776.aspx

You have to know the limitations of the data type in the relevant database. For that, read up on the scope and limitations of them for your database.
SQL Server: http://msdn.microsoft.com/en-us/library/ms187752.aspx
MySQL 5.1: http://dev.mysql.com/doc/refman/5.1/en/datetime.html
Oracle: http://www.techonthenet.com/oracle/datatypes.php

The key limitation is the supported range of values:

in MS SQL Server:
DATETIME: '1753-01-01 00:00:00.000' to '9999-12-31 23:59:59.997'
new since SQL 2008:
DATETIME2, DATETIMEOFFSET : '0001-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'

in MySQL:
DATE and DATETIME: '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP: '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07'  

in Oracle:
DATE and TIMESTAMP: '4712BC-01-01 00:00:00' to '9999-12-31 23:59:59'

Another limitation is the precision for the time:
in MSSQL Server:
DATETIME: 3 milliseconds
DATETIME2 and TIME: 100 nanoseconds

Ensure you pick the right data type for your database and date fields!

We won't discuss the TIMESTAMP type here; that is worth an article on its own.

In every database, you have very useful and basic functions to work with date and time. Know them, and use them.

MS SQL Server: http://msdn.microsoft.com/en-us/library/ms186724.aspx
MySQL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
Oracle: http://psoug.org/reference/date_func.html

You will notice that each database product has its own palette of tools, and unfortunately, almost all of them are named (slightly) differently.

The BIGGEST mistake of a newcomer to databases, when working with date and time, is to "ignore" the difference between how the data is stored, as opposed to how it is displayed. This is most often due to the fact that all database products have a certain level of tolerance for the "input" of data.

To put it bluntly: it is surely interesting how, exactly, the data is stored in the database bit-wise, but it is irrelevant to the how to code with dates and times.

What follows is a first code example of what is GOOD vs BAD. First, we create a table for the examples (in our case, Microsoft SQL Server 2005 Express Edition):

1:
2:
3:
4:
5:
6:
7:

CREATE TABLE orders 
( order_id int identity
, order_date datetime
, ordering_client_id int 
)
> Command(s) completed successfully.

Then we insert an "order" for today. No problem:

1:
2:
3:
4:

INSERT INTO orders ( order_date , ordering_client_id ) 
VALUES ( getdate(), 1 )
> (1 row(s) affected)

Then we insert an order for 31 August 2009, using the US mm/dd/yyyy format:

1:
2:
3:
4:
5:
6:
7:

INSERT INTO orders ( order_date , ordering_client_id ) 
VALUES ( '08/31/2009', 1 )
>Msg 242, Level 16, State 3, Line 1
>The conversion of a char data type to a datetime data type resulted 
>in an out-of-range datetime value.
>The statement has been terminated.

That code CAN work, as long as you connected with a login that has a language that says "us-english" (see step 7). Otherwise, you will probably run into the error shown.

Why is that so? In layman's terms: You have asked the database engine to take this -- '08/31/2009' -- and put it into a datetime field. But '08/31/2009' is not a date; it is a string. Yes, any human reader will tell you that is shows a date, but still it is a string, as the syntax tells: single quotes delimiter => string (in database terminology, it is called a varchar).

Try to guess how a database engine would interpret this date as: '01/02/03'
* 1 Feb 2003 ?
* 2 Jan 2003 ?
* 3 Feb 2001 ?

Enough playing; let's see "how to do it the right way".

in MS SQL Server, use the CONVERT function:

1:
2:
3:

INSERT INTO orders ( order_date , ordering_client_id ) 
VALUES ( CONVERT(DATETIME, '08/31/2009', 101 ), 1 )

What's that 101 in there? See here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

In MySQL, use STR_TO_DATE: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date

1:
2:
3:

INSERT INTO orders ( order_date , ordering_client_id ) 
VALUES ( STR_TO_DATE( '08/31/2009', '%m/%d/%Y' ), 1 )

in Oracle, use TO_DATE: http://www.techonthenet.com/oracle/functions/to_date.php

1:
2:
3:

INSERT INTO orders ( order_date , ordering_client_id ) 
VALUES ( TO_DATE( '08/31/2009', 'MM/DD/YYYY' ), 1 )

Using the syntax above, you will NEVER get any type conversion errors, assuming the input data IS a valid date. You are respecting the date limitations and the format specified.

As you surely notice, the CONVERT() function in MS SQL Server is quite limited for the style values/formats supported. If your "format" is not in there, you will have to first use some string manipulations to get the data "right".

To display the data, you could simply issue this:

1:
2:

SELECT * FROM orders

See Step 7 on what setting will influence how the date will be displayed (aka which format).

If you run this in some programming or reporting that knows and handles or formats the date/time as needed in the front-end, you won't have any problems. However, if you are using the SQL (view/procedure/function...) to control the format (for example, for exporting or emailing or whatever) you MUST control it yourself in the SQL.

So, let's do it explicitly:
MS SQL Server (same as above: http://msdn.microsoft.com/en-us/library/ms187928.aspx)

1:
2:
3:
4:
5:

SELECT order_date
, CONVERT(varchar(20), order_date, 101) us_style
, CONVERT(varchar(20), order_date, 103) uk_style 
FROM orders

MySQL: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

1:
2:
3:
4:
5:

SELECT order_date
, DATE_FORMAT(order_date, '%m/%d/%Y') us_style
, DATE_FORMAT(order_date, '%d/%m/%Y') uk_style 
FROM orders

Oracle: http://www.techonthenet.com/oracle/functions/to_char.php

1:
2:
3:
4:
5:

SELECT order_date
, TO_CHAR(order_date, 'MM/DD/YYYY') us_style
, TO_CHAR(order_date, 'DD/MM/YYYY') uk_style 
FROM orders

Do you want to get the orders from yesterday? Let's see how to do that. Remember the links above for the functions? They will be useful again!

MS SQL Server:

1:
2:
3:
4:

SELECT * FROM orders
WHERE order_date >= DATEADD(day, -1, CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120))
  AND order_date < CONVERT(datetime, CONVERT(varchar(10), getdate(), 120), 120)

MySQL:  

1:
2:
3:
4:

SELECT order_date FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) 
  AND order_date < CURDATE()

Oracle:

1:
2:
3:
4:

SELECT order_date FROM orders
WHERE order_date >= TRUNC(SYSDATE)-1
  AND order_date < TRUNC(SYSDATE)

As you can see, the code for MySQL is easy to read. The functions sysdate (Oracle) and getdate (SQL Server) return both date and time (despite the name). While Oracle's trunc() function will just set the time of the date parameter to '00:00:00', in MS SQL Server we need some expression like the one shown. There are other methods, like DATEADD(DAY, DATEDIFF(DAY, 0, getdate()), 0), for example.

You COULD also write like this to get the same results:

1:
2:
3:

SELECT * FROM orders
WHERE DATEDIFF(day, order_date, getdate()) = 0

BUT DO NEVER DO IT LIKE THAT. If you have an index on the order_date field, it couldn't be used!

Similarly, for orders for last year, you could be tempted to write:

1:
2:
3:

SELECT * FROM orders
WHERE DATEPART(year, order_date) = DATEPART(year, getdate()) - 1

A full table scan shall be the result. That's not good.

for SQL Server, you have only a little control:
SET DATEFORMAT : http://msdn.microsoft.com/en-us/library/ms189491.aspx
SET LANGUAGE: http://msdn.microsoft.com/en-us/library/ms174398.aspx
... defaulting to what language the login is set to.

MySQL is simple: it will only allow this format: 'YYYY-MM-DD HH:MI:SS' / 'YYYY-MM-DD'.
Any value not following that format will be silently changed to '0000-00-00 00:00:00'.

for Oracle, it is the session parameter NLS_DATE_FORMAT which controls how the implicit data type conversion is happening : http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams122.htm.

As seen above, you have settings that control the format, so there is just one small addition to this: month and day names.

In MS SQL Server, DATENAME(month, getdate()) will return the month's name, based on the language of the login (unless you have invoked a language using SET LANGUAGE)

In MySQL, you can control the language too: http://dev.mysql.com/doc/refman/5.1/en/locale-support.html

In Oracle, the setting NLS_DATE_FORMAT specifies the language, though you can specify, with the TO_CHAR() function, the language directly:

1:
2:
3:
4:

SELECT order_date
, TO_CHAR(order_date, 'DAY/MON/YYYY' ) 
, TO_CHAR(order_date, 'DAY/MON/YYYY', 'nls_date_language = FRENCH') 
FROM orders

As promised, no rocket science. Just simple good coding, and you won't have a problem with dates or time.