CODEWITHSUNDEEP
sqlsql-serverdate
John Smith
John Smith

Reputation: 2738

SQL date update query for sequential times

I have a database with a date field in text format. I want to update some of dates in my table. So, basically, the current date should change to new date, the thing is that I have at least 1000 records that need to be updated. The only advantage is that time in my date field is sequential.

I tried the following query: 

UPDATE my_table set Date='05-10-2012 00:00:00' WHERE Date like '%04-26-2012%'

but, it neglects the time section in my date field.

To illustrate of my intent 

    Current Date                     New Date
04-26-2012 02:45:00             05-10-2012 01:19:00
04-26-2012 02:47:00             05-10-2012 01:20:00
04-26-2012 02:48:00             05-10-2012 01:21:00
04-26-2012 02:49:00             05-10-2012 01:22:00
04-26-2012 02:50:00             05-10-2012 01:23:00

So, How can I update my date to the new date with the time section that I want ?

Upvotes: 1

Views: 6437

Answers (2)

dotjoe
dotjoe

Reputation: 26940

all data type issues aside, here ya go...

declare @from datetime, @thru datetime, @dateSeed datetime;
set @from = '4/26/2012';
set @thru = '4/27/2012';
set @dateSeed = '5/10/2012 1:19:00';

with cte as (
    select
        Date,
        rn = row_number() over(order by Date)
    from
        my_table
    where
            Date >= @from and Date < @thru
        --Date like '%04-26-2012%'
)

update cte set
    --110 = USA m/d/y format + 108 = hh:mm:ss 24 hour format
    --Date = convert(varchar(10), dateadd(mi, rn-1, @dateSeed), 110) + ' ' + convert(varchar(8), dateadd(mi, rn-1, @dateSeed), 108)
   Date = dateadd(mi, rn-1, @dateSeed)

Upvotes: 1

Durus
Durus

Reputation: 136

You can declare a variable before your update statement. To a minute before your starting time. Then increment the variable in the update statement before setting the field. I have pasted an example below.

declare @testData table (oldDate datetime)

insert into @testData (oldDate) values ('04-26-2012 02:45:00')

insert into @testData (oldDate) values ('04-26-2012 02:47:00')

insert into @testData (oldDate) values ('04-26-2012 02:48:00')

insert into @testData (oldDate) values ('04-26-2012 02:49:00')

insert into @testData (oldDate) values ('04-26-2012 02:50:00')


-- Set a default start date.

declare @newStartDate as dateTime

set @newStartDate = '05-10-2012 01:18:00'



update @testData set @newStartDate = DATEADD(MINUTE,1,@newStartDate),
    oldDate = @newStartDate

select * from @testData

Upvotes: 1

Related Questions