Using DATEPART without Select

Question

Post reply

Using DATEPART without Select

Robert Lassiter

SSC Rookie

Points: 40
More actions
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())

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

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

Dugi SSCoach Points: 17998 More actions · Answer 1

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]

matzer SSCrazy Eights Points: 9727 More actions · Answer 2

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

Robert Lassiter SSC Rookie Points: 40 More actions · Answer 3

This elegant simple solution works. Thank you very much!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

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]