Date Conversion Problem

Hi,

I've got a table with the columns DAY, MONTH and YEAR, all of them are numeric. I want to join them and store in a single datetime column.

I've been trying several ways to do that, but unfortunately I receive the same error :

"[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Probably I don't know how to use the CONVERT/CAST function properly.

Any ideas?

(Thanx)No, this error normally means that you are trying to convert values that are not an actual date.

11/31/03, or 2/29/2003, or 13/05/04, for example.

This happens when you have a database that does not store values in datetime format. It can also happen if you supply a day, month, and year value to SQL server in 2-digit formats and SQL server is unsure which value represents which part of the the date.

What code are you using for your conversion? You could wrap the ISDATE() function around it and query against your source table to find any values that it cannot convert.|||CAST(columnMonth AS Char(2)) + '/' + CAST(columnDay AS Char(2)) + '/' + CAST(columnYear AS Char(4))

returns the string which can be inserted into the field with DATETIME datatype|||STR(columnMonth, 2) + '/' + STR(columnDay, 2) + '/' + STR(columnYear, 4)

should work too.