SQL datetime update time to 00:00:00 from all other time values

sql sql-server-2008 tsql datetime milliseconds
AceAlfred picture AceAlfred · Oct 24, 2013 · Viewed 31.3k times · Source

Is there a way to update all columns in table from

2010-12-31 23:59:59.000

to

2010-12-31 00:00:00.000

Something like this??

UPDATE t1
SET [Posting Date] = [Posting Date] with ms = 00:00:00.000
WHERE ms = other than 00:00:00.000
GO

Answer

t-clausen.dk picture t-clausen.dk · Oct 24, 2013 
UPDATE t1
SET [Posting Date] = cast([Posting Date] as date)
WHERE cast([Posting Date] as time) > '00:00'

Related questions

How can I select the first day of a month in SQL?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code: select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
SQL Server SELECT INTO @variable?

I have the following code in one of my Sql (2008) Stored Procs which executes perfectly fine: CREATE PROCEDURE [dbo].[Item_AddItem] @CustomerId uniqueidentifier, @Description nvarchar(100), @Type int, @Username nvarchar(100), AS BEGIN DECLARE @TopRelatedItemId uniqueidentifier; SET @TopRelatedItemId = ( SELECT top(1) RelatedItemId FROM …

Read More