Date problem

I want to be able to set-up a job that runs every month on, let's say, the
15th. Now, that job will always look to the following month and pull every
transaction of a certain type that falls within that month. So, given that
the table I ma pulling from has dates attached to each record, is there a
way to generically have a script pull from the 'next' month? It sounds like
it should be fairly simple, but I can't figure it out.
Thanks for any help you can give me.
WillieDECLARE @.dt SMALLDATETIME;
-- remove time portion for today;
SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
-- move to the first day of this month;
SET @.dt = @.dt + 1 - DAY(@.dt);
-- add a month for next month;
SET @.dt = DATEADD(MONTH, 1, @.dt);
-- run query:
SELECT <column_list>
FROM <table_name>
WHERE <condition_list>
AND <date_column> >= @.dt
AND <date_column> < DATEADD(MONTH, 1, @.dt);
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:uMiOja3IGHA.2708@.tk2msftngp13.phx.gbl...
>I want to be able to set-up a job that runs every month on, let's say, the
>15th. Now, that job will always look to the following month and pull every
>transaction of a certain type that falls within that month. So, given that
>the table I ma pulling from has dates attached to each record, is there a
>way to generically have a script pull from the 'next' month? It sounds like
>it should be fairly simple, but I can't figure it out.
> Thanks for any help you can give me.
> Willie
>|||Ah yes, that makes sense. Or I guess I could do a date part on the month as
well. Thanks!
wb
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ODCHeh3IGHA.1760@.TK2MSFTNGP10.phx.gbl...
> DECLARE @.dt SMALLDATETIME;
> -- remove time portion for today;
> SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
> -- move to the first day of this month;
> SET @.dt = @.dt + 1 - DAY(@.dt);
> -- add a month for next month;
> SET @.dt = DATEADD(MONTH, 1, @.dt);
> -- run query:
> SELECT <column_list>
> FROM <table_name>
> WHERE <condition_list>
> AND <date_column> >= @.dt
> AND <date_column> < DATEADD(MONTH, 1, @.dt);
>
>
>
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:uMiOja3IGHA.2708@.tk2msftngp13.phx.gbl...
>|||> Ah yes, that makes sense. Or I guess I could do a date part on the month
> as well.
However, (a) that won't be sargable (you won't be able to use an index), and
(b) you'll also have to do year as well, else you will get rows from
February last year, and February the year before, etc.
Trust me, a range query is the better play here.|||Brilliant, you got me thinking and this seems to work great
AND DatePart(mm,CP.dtPurchaseDate) = DatePart(mm,getdate())+1
unless somebody can think of some reason this might puke?
Willie
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ODCHeh3IGHA.1760@.TK2MSFTNGP10.phx.gbl...
> DECLARE @.dt SMALLDATETIME;
> -- remove time portion for today;
> SET @.dt = 0 + DATEDIFF(DAY, 0, GETDATE());
> -- move to the first day of this month;
> SET @.dt = @.dt + 1 - DAY(@.dt);
> -- add a month for next month;
> SET @.dt = DATEADD(MONTH, 1, @.dt);
> -- run query:
> SELECT <column_list>
> FROM <table_name>
> WHERE <condition_list>
> AND <date_column> >= @.dt
> AND <date_column> < DATEADD(MONTH, 1, @.dt);
>
>
>
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:uMiOja3IGHA.2708@.tk2msftngp13.phx.gbl...
>|||> unless somebody can think of some reason this might puke?
Yes!
INSERT CP(dtPurchaseDate) SELECT '19780201';
Again, use a RANGE QUERY with REAL (SMALL)DATETIME values. DatePart
shouldn't really be used for this kind of query (though it would make sense
if you were trying to get all purchases made in February across all years).|||Thanks, I think our last posts crossed in midstream. I see your point now.
Thanks again for the help!
wb
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXP4xA5IGHA.424@.TK2MSFTNGP12.phx.gbl...
> Yes!
> INSERT CP(dtPurchaseDate) SELECT '19780201';
> Again, use a RANGE QUERY with REAL (SMALL)DATETIME values. DatePart
> shouldn't really be used for this kind of query (though it would make
> sense if you were trying to get all purchases made in February across all
> years).
>