Hi All,
I have a Datetime field that I need to convert to just date. When I run the following query as a select, it works fine. As an update, nothing changes. Please let me know where I am going wrong as my brain is frozen.
update SITE set DATE = convert(varchar(10),DATE,101)
Thanks a lot.
You'll need to change the data type on the column with an ALTER TABLE statement. If the column is still set as a DATETIME, SMALLDATETIME or DATETIME2, you need to make it into a DATE to eliminate the storage of a time component. Further, since you already have data in the table, the best way to do it would be to add the column through the ALTER TABLE statement and not try to change the data type. But that would leave the old column. Instead, I'd take the approach of creating a new table with the correct data type, move the data from one table to the other, drop all the constrants on the old table, recreate them on the new table, rename the old table to something else, rename the new table to the old table's name. That's the safest way. At no point have you dropped the old table or altered it's data. Now you validate that everything moved and then drop the old table.
If you want to change the field, then use:
ALTER TABLE SITE
ALTER COLUMN [DATE] date;
If you want to just update the field to remove a time component, use:
UPDATE SITE SET [DATE] = DATEADD(day,DATEDIFF(day,0,[DATE]),0);
But it sounds like you need the first one.
