Time part of datetime to be the same

SQL 2008

I have data in a datetime field (examples as follows)

2014-10-26 00:00:00.000
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000

I want the time part to be 00:00:00.000 for every date in the field

So the data would be

2014-10-26 00:00:00.000
2014-10-29 00:00:00.000
2014-10-29 00:00:00.000
2014-10-29 00:00:00.000

Thanks
Mark WilsonBI DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
CAST as a date, which removes the TIME component
UPDATE your_table
SET your_date_column = CAST(your_date_column as date)

Open in new window


Or just change the column data type to date
ALTER your_table ALTER COLUMN your_date_column DATE

Open in new window


>I want the time part to be 00:00:00.000 for every date in the field
You'll need to be more specific.  Change the value?  Change the data type?  One time deal but leave alone the ability to have other datetime values?  Is this column datetime, or something like varchar?
atulvjain1Commented:
try this
this will chnage your datetime to date  with time 00000


SELECT getdate(),dateadd(ms,-1*DATEPART(ms,GETDATE()),dateadd(hh, -1*DATEPART(hh,GETDATE())  ,dateadd(mi,-1*DATEPART(mi,GETDATE()) ,dateadd(ss,-1*DATEPART(ss,GETDATE()) ,GETDATE()))))
Jim HornMicrosoft SQL Server Data DudeCommented:
>I want the time part to be 00:00:00.000 for every date in the field
I think we've nailed the conversion part of this question, so if you could further define what you mean by the above (select, change, ...) we're home.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Scott PletcherSenior DBACommented:
The command below will reset the time back to 00:00:00.000 (midnight).

UPDATE table_name
SET datetime_column = DATEADD(DAY, DATEDIFF(DAY, 0, datetime_column), 0)

Or you use that to strip the time in a SELECT list:

SELECT DATEADD(DAY, DATEDIFF(DAY, 0, datetime_column), 0) AS datetime, ...
FROM table_name
WHERE ...
PortletPaulEE Topic AdvisorCommented:
IF you are looking for ways to accurately filter data by a date range, then you do NOT have to alter your data to suit the range criteria. Instead alter the criteria to suit the data.

your_table
dt_created
2014-10-26 00:00:00.000
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000

Let's say you want all transactions on 2014-10-29, so:

select *
from your_table
where ( dt_created >= '20141029' and dt_created < '20141030' )

result:
dt_created
2014-10-29 01:00:00.000
2014-10-29 02:00:00.000
2014-10-29 00:00:00.000

what you should NOT do is either of these (as they will be slower):

where CAST(dt_created AS DATE) = '20141029'

where DATEADD(DAY, DATEDIFF(DAY, 0, dt_created), 0) = '20141029'

This is not to suggest that the answers before this are incorrect! Both the cast() or dateadd() approaches work for the suggestion actions. I am only discussing filtering by a date range.

For more on this topic please see: "Beware of Between"
atulvjain1Commented:
please run
following query in SQL and you will get the idea

SELECT getdate(),dateadd(ms,-1*DATEPART(ms,GETDATE()),dateadd(hh, -1*DATEPART(hh,GETDATE())  ,dateadd(mi,-1*DATEPART(mi,GETDATE()) ,dateadd(ss,-1*DATEPART(ss,GETDATE()) ,GETDATE()))))

above sql will return following result

2015-03-13 04:53:47.333      2015-03-13 00:00:00.000
you just need to replace Gtedate() to your date colom.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
halifaxman, do you still need help on this question?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.