-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|