locked
update Minutes value in the datetime column RRS feed

  • Question

  • Hello,

    I have a column which has multiple datetime stamps, my task is for any value that has 21 mins, I have to update it to 30 mins.

    it is from prod data, so earlier datadd funtion was added wrong, so any values Min's - 21 has to be updated.

    it is datetime column

    can any one help?

    Thanks


    Surya

    • Changed type Naomi N Thursday, April 12, 2012 8:48 PM Question rather than discussion
    Thursday, April 12, 2012 8:25 PM

Answers

  • Please try:

    Declare @myTable Table (ColDateTime DateTime)
    Insert Into @myTable
    Select '04/12/2012 09:21:20' Union All 
    Select '04/12/2012 10:10:20' Union All 
    Select '04/12/2012 11:21:40' Union All 
    Select '04/12/2012 12:11:10' Union All 
    Select '04/12/2012 13:21:10' 
    
    Update MainQry 
    Set ColDateTime = Convert(DateTime, Replace(ColDateTime, 21, 30))
    From @myTable As MainQry 
    Where DatePart(Minute, ColDateTime) = 21
    
    Select * 
    From @myTable
    
    --output
    ColDateTime
    2012-04-12 09:30:00.000
    2012-04-12 10:10:20.000
    2012-04-12 11:30:00.000
    2012-04-12 12:11:10.000
    2012-04-12 13:30:00.000


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by John Fend Thursday, April 12, 2012 9:07 PM
    Thursday, April 12, 2012 8:52 PM
  • Try (may be slow)

    select *, dateadd(minute, 9, dateTimeField) from myTable where datepart(minute, DateTimeField) = 21

    I assume that you know your field(s) name. If not, we may need to write a similar dynamic SQL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Daniel_Steiner Thursday, April 12, 2012 8:53 PM
    • Marked as answer by Kalman Toth Thursday, April 12, 2012 9:09 PM
    Thursday, April 12, 2012 8:50 PM

All replies

  • Any help pls?


    Surya

    Thursday, April 12, 2012 8:43 PM
  • Could you please post some sample data and desired output?

    Are you looking for any datetime value in your table which is like "XX-XX-XXXX XX:21:XX" 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, April 12, 2012 8:45 PM
  • yes pls 04-12-2012 hh:21:ss"  , we may have multiple values like this, we have to replace 21 with 30

    Thanks


    Surya

    Thursday, April 12, 2012 8:47 PM
  • Try (may be slow)

    select *, dateadd(minute, 9, dateTimeField) from myTable where datepart(minute, DateTimeField) = 21

    I assume that you know your field(s) name. If not, we may need to write a similar dynamic SQL.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Proposed as answer by Daniel_Steiner Thursday, April 12, 2012 8:53 PM
    • Marked as answer by Kalman Toth Thursday, April 12, 2012 9:09 PM
    Thursday, April 12, 2012 8:50 PM
  • Please try:

    Declare @myTable Table (ColDateTime DateTime)
    Insert Into @myTable
    Select '04/12/2012 09:21:20' Union All 
    Select '04/12/2012 10:10:20' Union All 
    Select '04/12/2012 11:21:40' Union All 
    Select '04/12/2012 12:11:10' Union All 
    Select '04/12/2012 13:21:10' 
    
    Update MainQry 
    Set ColDateTime = Convert(DateTime, Replace(ColDateTime, 21, 30))
    From @myTable As MainQry 
    Where DatePart(Minute, ColDateTime) = 21
    
    Select * 
    From @myTable
    
    --output
    ColDateTime
    2012-04-12 09:30:00.000
    2012-04-12 10:10:20.000
    2012-04-12 11:30:00.000
    2012-04-12 12:11:10.000
    2012-04-12 13:30:00.000


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by John Fend Thursday, April 12, 2012 9:07 PM
    Thursday, April 12, 2012 8:52 PM
  • thanks, but the where condition doesn't work

    where datepart(minute, colname) = 21


    Surya

    Thursday, April 12, 2012 8:55 PM
  • thanks, but the where condition doesn't work

    where datepart(minute, colname) = 21


    Surya

    Why????

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, April 12, 2012 8:57 PM
  • Works for me:

    Declare @myTable Table (ColDateTime DateTime)
    Insert Into @myTable
    Select '04/12/2012 09:21:20' Union All 
    Select '04/12/2012 10:10:20' Union All 
    Select '04/12/2012 11:21:40' Union All 
    Select '04/12/2012 12:11:10' Union All 
    Select '04/12/2012 13:21:10' 
    
    select *, DATEADD(minute, 9, ColDateTime) as [Fixed Datetime]
    From @myTable As MainQry 
    Where DatePart(Minute, ColDateTime) = 21


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 12, 2012 8:57 PM
  • Also my task is to replace 21 with 30, not adding value.

    Surya

    Thursday, April 12, 2012 8:58 PM
  • I was showing you an example hoping you'll figure out how to turn SELECT (you need it to test first) into UPDATE.

    UPDATE myTable SET DateCol = dateadd(minute, 9, DateCol)

    where datepart(minute, DateCol) = 21


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 12, 2012 8:59 PM
  • Also my task is to replace 21 with 30, not adding value.

    Surya

    Did you check my solution? 

    Either or of provided solutions has to work. Unless you are looking for something else.


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, April 12, 2012 9:03 PM
  • Thanks Arbi, it works.


    Surya

    Thursday, April 12, 2012 9:06 PM
  • Hi ,

    Arbi and Naomi solutions are a god way to do , but if you want to do old school way by simply replacing 21 with 30 with out a where clause

    try this

    Declare @myTable Table (ColDateTime DateTime)
    Insert Into @myTable
    Select '04/12/2012 09:21:20' Union All 
    Select '04/12/2012 10:10:20' Union All 
    Select '04/12/2012 11:21:40' Union All 
    Select '04/12/2012 12:11:10' Union All 
    Select '04/12/2012 13:21:10' 
    
    
    select   convert(datetime,convert(varchar,coldatetime,101 )+' '+ replace(convert(varchar,coldatetime,108),':21:',':30:')) 
    
    from @mytable

    Thursday, April 12, 2012 9:16 PM