|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Date problemI 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 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); Show quote "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 Show quote "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... >>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 However, (a) that won't be sargable (you won't be able to use an index), and > as well. (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 Show quote "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... >>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 >> > > > 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 Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uXP4xA5IGHA.424@TK2MSFTNGP12.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). >  |
|||||||||||||||||||||||
Other interesting topics