[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Statment

Posted on 2013-06-20
7
Medium Priority
?
305 Views
Last Modified: 2013-06-21
In my sql server table i have fields where the data looks like this:

2007-08-25 12:03:43.333

I don't want the time values so how do i update so they look like this

2007-08-25 00:00:00.000
0
Comment
Question by:vbnetcoder
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 1

Accepted Solution

by:
RCWade earned 2000 total points
ID: 39263369
Try this:

select DATEADD(Day, DATEDIFF(Day, 0, '2007-08-25 12:03:43.333'), 0)
0
 

Author Comment

by:vbnetcoder
ID: 39263376
I need to do this to ALL the rows for a specific field.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 39263401
update table
set column = DATEADD(Day, DATEDIFF(Day, 0, Column), 0)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39263448
update <table>
set <column> = convert( datetime, convert( varchar, <column>, 112 ), 121 );

Edited to reflect update.  Clever uses of those, ewangoya =)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39264963
I get a little concerned when folks tell me their field "looks like" (a date)

Is the field a datetime data type, or is it a string of characters (varchar or nvarchar)?
either can "look like" what you describe, but they differ a LOT.

Then, there is the following question:
WHY  update that data this to remove time? - curious thing to do.
You will loose precision (e.g. in ordering data)
IF you are doing this for date range filtering - I wouldn't
IF your field is a datetime data type (a "real datetime field') then
update table
set column = DATEADD(Day, DATEDIFF(Day, 0, Column), 0) -- all versions
or
update table
set column = convert(date,Column) -- sql 2008+
IF your field is a string data type (varchar or nvarchar) then
update table
set column = convert( varchar(10), Column , 112 ) + ' 00:00:00'
or (to remove time completely)
update table
set column = convert( varchar(10), Column , 112 )
Whatever you do here - please check the actual field data type first
test
backup
make absolutely certain you want to do this
think about it
when utterly certain - only then do it

more at: http://sqlfiddle.com/#!3/1cfd2/3

{+ edit, btw if you do choose DATEADD(Day, DATEDIFF(Day, 0, Column), 0)  
all points to ewangoya please  - it's a good method, v.fast too }
0
 
LVL 1

Expert Comment

by:yechan
ID: 39266699
A somewhat radical idea is to change the data type of the column from DateTime to just Date.
0
 

Author Closing Comment

by:vbnetcoder
ID: 39266713
this gave me what i needed i was able to figure out the rest. TY
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question