Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2005 - Updating date for a datetime column but keeping the time

Posted on 2011-03-24
4
Medium Priority
?
212 Views
Last Modified: 2012-08-14
Hi Experts,

I have a datetime column that has the same date for all rows but with different time. The column value of the first row looks like this 22/03/2011 8:00:00 AM

I want to change the date to 01/01/1900 without changing the time, how can I do that?
0
Comment
Question by:feesu
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 35206565
update yourtable
  set yourfield = dateadd(day, datediff(day, yourfield, 0 ), yourfield)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35206586
select convert( datetime, convert(float, myDateColumn ) - convert(int, myDateColumn ))
from myTable
0
 
LVL 18

Expert Comment

by:deighton
ID: 35208093
update YourTable
 SET YourField = CAST(CAST('01-01-1900' AS float) + CAST(YourField as Float) - FLOOR( CAST(YourField as Float)) AS DATETIME)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35214701
angelIII,

Good one.
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

885 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