?
Solved

SQL Statment

Posted on 2013-06-20
7
Medium Priority
?
307 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
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

840 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