CODEWITHSUNDEEP
sqlsql-server
Mario
Mario

Reputation: 335

Update datetime fields date only

Years ago a conversion from MSSQL 6.5 to MSSQL 2000 has been done and they realized just this week that the conversion failed to convert some datetime columns. It is now my task to fix that and I've been scratching my head on how I could preserve some pieces of information I know is accurate. Here is the structure of one of the table I need to fix.

DateTimeField1         DateTimeField2         DateTimeField3
01/01/1900 5:50:00 PM  01/01/1900 5:52:00 PM  15/02/2005 12:00:00 AM

This is one sample of the many records that are corrupted, unfortunatly I don't have access of any backup from before the conversion. As you can see, the date part is the default value for a DateTime field and is the part I need to fix. I have the following select, which gives me the rows I need to fix.

SELECT DateTimeField1, DateTimeField2, DateTimeField3
FROM Table1
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101')

Now assume I have 60 records resulting from the select. I need to update those records based on the DateTimeField3 DATE part only. The sample above would look like;

DateTimeField1         DateTimeField2         DateTimeField3
15/02/2005 5:50:00 PM  15/02/2005 5:52:00 PM  15/02/2005 12:00:00 AM

Any idea on how to achieve this?

Upvotes: 1

Views: 4574

Answers (6)

AdamL
AdamL

Reputation: 13191

The simplest way to do this would be:

update Table1
    set 
         DateTimeField1 = cast(cast(DateTimeField1 as float)-floor(cast(DateTimeField1 as float)) + floor(cast(DateTimeField3 as float)) as datetime
    WHERE DateTimeField1 < '20000101'

update Table1
    set 
        DateTimeField2 = cast(cast(DateTimeField2 as float)-floor(cast(DateTimeField2 as float)) + floor(cast(DateTimeField3 as float)) as datetime
    WHERE DateTimeField2 < '20000101'

I know it'd work for SQL Server 2005 and 2008, but I'm not sure about 2000 edition, so test this first.

The explanation is this: datetime is stored as float value, where int part is date, and decimal part is time. So by floor(cast(DateTimeField3 as float)) you get date part, and you can simply add this to DateTimeField1 and DateTimeField2, after you subtracted date part from them. For 1900-01-01 date part would be zero: select cast(0 as datetime), but it'll still work.

Upvotes: 0

KaraokeStu
KaraokeStu

Reputation: 768

This should do what you need:

update <yourtable>
set 
    DateTimeField1 = case when cast('1 jan 1900' as datetime) = cast(floor(cast(DateTimeField1 as float)) as datetime) then DateTimeField1 + DateTimeField3 else date1 end,
    DateTimeField2 = case when cast('1 jan 1900' as datetime) = cast(floor(cast(DateTimeField2 as float)) as datetime) then DateTimeField2 + DateTimeField3 else date2 end
where DateTimeField1 < '2 jan 1900' or DateTimeField2 < '2 jan 1900'

This works by checking to see if the date and time, converted to a float and then floored (which removes the time part) equals 1st Jan 1900.

As the date part of DateTimeField1 or DateTimeField2 are essentially 0 and the time part of DateTimeField3 is 0, you can simply add the two together.

Upvotes: 0

Parag Meshram
Parag Meshram

Reputation: 8521

I believe you want to only update DateTimeField1 & DateTimeField2 when they are less than '20000101'. CASE Statement will take care of not updating wrong field.

Try single query UPDATE -

SQL SERVER 2008 AND LATER -

UPDATE  Table1

SET DateTimeField1 = (CASE WHEN (DateTimeField1 < '20000101') 
                      THEN CAST(CAST (DateTimeField3 AS DATE) AS DATETIME)
                      + CAST (DateTimeField1 AS TIME)
                      ELSE DateTimeField1
                  END)
, DateTimeField2 = (CASE WHEN (DateTimeField2 < '20000101') 
                      THEN CAST(CAST (DateTimeField3 AS DATE) AS DATETIME)
                      + CAST (DateTimeField2 AS TIME)
                      ELSE DateTimeField2
                  END)
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101');

EARLIER THAN SQL SERVER 2008 -

UPDATE Table1 

SET DateTimeField1 = (CASE WHEN (DateTimeField1 < '20000101') 
                      THEN DATEADD(DAY, 0, DATEDIFF(day, 0, DateTimeField3)) 
                      + DATEADD(DAY, 0 - DATEDIFF(day, 0, DateTimeField1), DateTimeField1)
                      ELSE DateTimeField1
                 END)
, DateTimeField2 = (CASE WHEN (DateTimeField2 < '20000101') 
                      THEN DATEADD(DAY, 0, DATEDIFF(day, 0, DateTimeField3)) 
                      + DATEADD(DAY, 0 - DATEDIFF(day, 0, DateTimeField2), DateTimeField2)
                      ELSE DateTimeField2
                 END)
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101');

Upvotes: 0

unlimit
unlimit

Reputation: 3752

Try something like this, it should work on MSSQL 2000

UPDATE tab SET DateTimeField1 = 
ltrim(str(datepart(year, DateTimeField3))) + '-' + 
ltrim(str(datepart(month, DateTimeField3))) + '-' + 
ltrim(str(datepart(day, DateTimeField3))) + ' ' +
ltrim(str(datepart(hour, DateTimeField1)))  + ':' + 
ltrim(str(datepart(minute, DateTimeField1))) + ':' + 
ltrim(str(datepart(second, DateTimeField1))) + '.' + 
ltrim(str(datepart(millisecond, DateTimeField1))) ,
DateTimeField2 = ltrim(str(datepart(year, DateTimeField3))) + '-' + 
ltrim(str(datepart(month, DateTimeField3))) + '-' + 
ltrim(str(datepart(day, DateTimeField3))) + ' ' +
ltrim(str(datepart(hour, DateTimeField2)))  + ':' + 
ltrim(str(datepart(minute, DateTimeField2))) + ':' + 
ltrim(str(datepart(second, DateTimeField2))) + '.' + 
ltrim(str(datepart(millisecond, DateTimeField2)))
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101')

Upvotes: 0

Andrey Gordeev
Andrey Gordeev

Reputation: 32559

Use this query:

SELECT DateTimeField1 = 
    convert(datetime,convert(int,convert(float,t.DateTimeField3)) 
    + convert(float,t.DateTimeField1)),
       DateTimeField2 = 
    convert(datetime,convert(int,convert(float,t.DateTimeField3)) 
    + convert(float,t.DateTimeField2)),
FROM Table1 t
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101')

SQL Server stores datetime as a float, where a right side is a time and a left side is a date. This query replaces the left side of the wrong datetime by the left side of the correct datetime

Upvotes: 0

Joe White
Joe White

Reputation: 97848

If one field is always 1900-01-01 but with the correct time, and the other field is 12:00:00 AM but with the correct date, you can just add them together.

UPDATE Test
SET
  DateTimeField1 = DateTimeField1 + DateTimeField3,
  DateTimeField2 = DateTimeField2 + DateTimeField3
WHERE (DateTimeField1 < '20000101') OR (DateTimeField2 < '20000101')

See this SQL Fiddle.

1900-01-01 is the "zero" date, so if you add it to something else, you get that same value. 12:00:00 AM is the "zero" time.

If there are cases where DateTimeField1 has the correct date but DateTimeField2 doesn't, you might want to do this as two separate queries.

Upvotes: 1

Related Questions