Update a datetime column

I'm trying to update a datetime column from another datetime column. However, I just want the date transferred to the new column without the time. Any ideas? Thanks for your help.

Try this

Update Table set DateTimeField = DateAdd(day,DateDiff(day,DateTimeField,OtherDateTi meField),DateTimeField)

Basically you add to your field the delta in days between the two datetime fields

Robert

I'm trying to populate a new datetime column with the mm/dd/yyyy from another datetime column that has mm/dd/yyyy hh:mm:ss AM/PM. Is there a way to update this empty column with just the mm/dd/yyyy?

You can just use CONVERT:

CONVERT(char(10), YourDateTimeColumn, 101)

Nosepicker's suggestion is right on the money, but you should realize that SQL Server has no concept of a date without a time. If you enter just a date with no time, SQL Server assumes that the time is midnight. So if you update the column with a date of 4/21/2005, SQL Server see it as 4/21/2005 0:00:00.0000 AM.

This distinction is very important when you start comparing date values.

Thank you for your help. I wish SQL would incorporate two additional field types (Date Only, Time Only). Changing the date to CHR will not allow a query structure such as "DATE > 2005/01/02".

I agree, I wish there was date only and time only data types. You can do a query where you look simply say greater than the date value, but again, SQL Server will assume that you mean 12 AM on that date.