Solved

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

Posted on 2011-03-24
4
208 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 500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSIS GUID Variable 2 30
Copy data to New Year 9 29
SP result not being displayed 5 36
Need SSIS project 2 23
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

685 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