Date range for daily job

Hi!
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
>
>
>
>
Thanks. It works.sql

Date range for daily job

Hi!
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.
T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegro ups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>
|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegro ups.com...
>
>
Thanks. It works.

Date range for daily job

Hi!
I have a SQL job that runs a query. That job has to be run daily (or
couple of times per day) The query has to collect information for some
created apps
WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
[Right Now]. How can dynamically program the First Day of Current
Month at 12:00am. Anybody know of any function that can give me this
value no matter at what time I run that job?
Thank you,
T.T.
declare @.date datetime
-- Figure out the first day of the month
select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
-- Trim off the time.
select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
SELECT @.date
RLF
"tolcis" <nytollydba@.gmail.com> wrote in message
news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
> Hi!
> I have a SQL job that runs a query. That job has to be run daily (or
> couple of times per day) The query has to collect information for some
> created apps
> WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> [Right Now]. How can dynamically program the First Day of Current
> Month at 12:00am. Anybody know of any function that can give me this
> value no matter at what time I run that job?
> Thank you,
> T.
>|||On Mar 15, 9:41 am, "Russell Fields" <russellfie...@.nomail.com> wrote:
> T.
> declare @.date datetime
> -- Figure out the first day of the month
> select @.date = DATEADD(DD, (DATEPART(DD, GETDATE()) - 1) * -1, GETDATE())
> -- Trim off the time.
> select @.date = CONVERT(DATETIME,CONVERT(VARCHAR(10), @.date, 101))
> SELECT @.date
> RLF
> "tolcis" <nytolly...@.gmail.com> wrote in message
> news:1173964963.027568.47310@.n59g2000hsh.googlegroups.com...
> > Hi!
> > I have a SQL job that runs a query. That job has to be run daily (or
> > couple of times per day) The query has to collect information for some
> > created apps
> > WHERE DateCreated BETWEEN [First Day of Current Month at 12:00am] AND
> > [Right Now]. How can dynamically program the First Day of Current
> > Month at 12:00am. Anybody know of any function that can give me this
> > value no matter at what time I run that job?
> > Thank you,
> > T.
Thanks. It works.

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).
>

Date issue in a SQL 2005 scheduled job

I have a job in SQL 2005 that when it runs works fine if I hard code the Makedate, what I need it to do is have the Makedate equal todays date minus one day...I came up with the code below, but that does seem to work...any thoughts.

INSERT INTO abcTransaction
(
Payee,
Payment,
AccountNumber
)
SELECT
'Credit',
SUM(Rebate * Quantity),
A.AccountNumber
FROM
Account A
INNER JOIN
abdTrades T on A.AccountNumber = T.AccountNumber
Where MakeDate = getDate() - 1

GROUP BY
A.AccountNumber

Looks good. What is the error message?|||

Try:

Where MakeDate = DATEADD(day, -1, getDate())

Keep in mind, you are just subtracting one day - 24 hours. So if the underlying value for your date is 1/10/2007 15:00, then the minus one day produces 1/9/1007 15:00. So if you really want for the whole previous day try:

Where MakeDate = CAST(MONTH(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(DAY(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(YEAR(DATEADD(day, - 1, GETDATE())) AS varchar))

This is kind of messy and Ihave not found a better way. I usually wrap it into a SQL function.

|||

cloris:

Keep in mind, you are just subtracting one day - 24 hours. So if the underlying value for your date is 1/10/2007 15:00, then the minus one day produces 1/9/1007 15:00. So if you really want for the whole previous day try:

Where MakeDate = CAST(MONTH(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(DAY(DATEADD(day, - 1, GETDATE())) AS varchar) + '/' + CAST(YEAR(DATEADD(day, - 1, GETDATE())) AS varchar))

This is kind of messy and Ihave not found a better way. I usually wrap it into a SQL function.

If you need for entire previous day you can do it like this:

Where MakeDate >= convert(Varchar, Getdate() -1, 101) And MakeDate < convert(Varchar, Getdate() , 101)

|||

Will try this as it does need to be for the entire previous day, not a 24 hour cycle.

|||Much cleaner... I am filing this one away.