?
Solved

SQL Statment

Posted on 2013-06-20
7
Medium Priority
?
303 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

800 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