Post reply

How to delete a row if date and time are older than a spacific date and time

  • September 6, 2006 at 8:52 pm

    #114779

    Hi

    I want to have a row deleted using a SP when the the date and time is older than the current date and time ie: if a date and time is older that 8 am every morning then it should be deleted. I have a column (datein-[datetime] and timein [char].

    Any help would be appreciated

    Thanks

    Greg

  • September 6, 2006 at 9:24 pm

    #658947

    Will this help ?

     

    DELETE A FROM TABLE_NAME A

    WHERE CONVERT(VARCHAR(8),DATEIN,112) < CONVERT(VARCHAR(8),GETDATE(),112)

    AND TIMEIN < 8

    Regards,

    Ganesh

  • September 6, 2006 at 9:36 pm

    #658949

    Thank for this

    I get this error in QA

    Server: Msg 245, Level 16, State 1, Procedure CleanWho'sOutList, Line 10

    Syntax error converting the varchar value '6:15 a.m. ' to a column of data type int.

    Greg

  • September 6, 2006 at 9:51 pm

    #658951

    Try this...

     

    DELETE A FROM TABLE_NAME A

    WHERE CONVERT(VARCHAR(8),DATEIN,112) < CONVERT(VARCHAR(8),GETDATE(),112)

    AND CAST(substring(TIMEIN,1,patindex('%:%',TIMEIN)-1) AS INT) < 8

    Regards,

    Ganesh

  • September 6, 2006 at 10:21 pm

    #658955

    Cheers NewBie

    This worked but did leve and entry from 31/08/06 @ 9am

    Greg

  • September 6, 2006 at 10:42 pm

    #658958

    Try this:

    Delete tableName where Cast(Convert(Char(10),DateIN,101) + ' ' + Cast(TIMEIN as varchar(10)) as datetime) <=  Cast(Convert(Char(10),getdate(),101) + ' 08:00:00' as Datetime)

    Sreejith

  • September 7, 2006 at 11:54 am

    #659180

    You can also try this...

     

    DELETE A FROM TABLE_NAME A

    WHERE CONVERT(VARCHAR(8),DATEIN,112) < CONVERT(VARCHAR(8),GETDATE(),112)

    DELETE A FROM TABLE_NAME A

    WHERE CONVERT(VARCHAR(8),DATEIN,112) = CONVERT(VARCHAR(8),GETDATE(),112)

    AND CAST(substring(TIMEIN,1,patindex('%:%',TIMEIN)-1) AS INT) < 8

    Regards,

    Ganesh

  • October 1, 2006 at 12:35 pm

    #663518

    Thanks for all your help guys. Much appreciated.

    Greg

  • October 1, 2006 at 5:24 pm

    #663523

    If you're never gonna exceed capasity of Excel spreadsheet you can use the approaches above. (Why you need SQL Server then?)

    But for real databases:

    1) you better don't separate date and time;

    2) while you have it saparated use at least 1 index of 2:

    DELETE FROM TABLE_NAME

    WHERE DATEIN < GETDATE() - CONVERT(datetime, TIMEIN, 108)

     

    _____________
    Code for TallyGenerator

  • October 1, 2006 at 9:20 pm

    #663528

    Greg,

    It would appear that your TimeIn column could have the following formats...

    6:15 a.m.

    12:15 p.m.

    6:15 p.m.

    12:15 a.m.

    The periods in the "a.m." and "p.m." could make this a bugger to convert.  Even the COVERT 108 thing won't work.  You have to get rid of the periods before you can do anything with the time... the "a.m." and "p.m." are absolutely necessary, though, because the time is not 24 hour time.  So we need to keep them... and we force the date and time because you may decide to run this after 8 and not right at 8...

     DELETE yourtable

      WHERE @DateIn + REPLACE(@TimeIn,'.','')

          < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) + '08:00 AM'

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply