Post reply

Using DATEPART without Select

  • January 8, 2010 at 11:27 pm

    #386067

    I have created tables named for each month. January, February etc. Once per month I will delete all records in the current months table because they are from a year ago, and insert new records. I don't want to have 12 scripts. Can I use DATEPART to find the month and use in a statement like this?

    delete from DATEPART(month, GETDATE())

  • January 9, 2010 at 12:07 am

    #1101318

    I'm not sure what you are trying to do, but it doesn't work!

    Use something like this

    DELETE FROM TABLE

    WHERE your_filed = DATEPART(...)

    Maybe you can find the required results.

    Anyway post the Table structure, some test data and results how you want to see!

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • January 9, 2010 at 12:11 am

    #1101319

    You can use dynamic SQL to do this i.e.

    set language English

    declare @qry nvarchar(max)

    select @qry = 'delete from ' + datename(month, getdate())

    exec (@qry)

    Use set language to be sure you get the month names in English.

    Peter

  • January 9, 2010 at 1:12 am

    #1101323

    This elegant simple solution works. Thank you very much!

  • January 9, 2010 at 3:10 am

    #1101339

    Robert Lassiter (1/8/2010)

    I have created tables named for each month. January, February etc. Once per month I will delete all records in the current months table because they are from a year ago, and insert new records. I don't want to have 12 scripts. Can I use DATEPART to find the month and use in a statement like this?

    delete from DATEPART(month, GETDATE())

    Out of curiosity Robert, why do you have 12 tables, and not one table with an extra column labelled "Month"?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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