Solved

SQL Statment

Posted on 2013-06-20
7
300 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 500 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

 
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 48

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Regarding Disk IO 3 49
SQL Instance service gone? 5 39
Split string into 3 separate fields 5 22
Specify timing interval fro change data 2 57
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 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