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 Parameter and text box question

I have a report that runs transcripts for people. I have a date parameter setup along with last and first name. The date parameter field is used to specify what year (ex. 1/1/2006 - 12/31/2006) to pull the information for.

Now..I have a text box at the bottom (Footer) of my report that says something to the affect of "Official transcripts for 2006". There will be times when I have to run the report using a different year other then "2006" in the date parameter field. How do I have the text at the bottom change to reflect the year I'm reporting on? If I use dates say in 2004 I need the text at the bottom to reflect that year and not 2006. Can this be accomplished?

Thx,

Bill

Sounds like you need to parse the parameter value (Parameters!ParameterName.Value) to get to the year portion. You can add a code-behind VB.NET function to help with this as demonstrated at the beginning of this article.|||

="Official transcripts for 2006" & Parameters!YourDateParameterName.Value

Try this. . .

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.

Date formats

I am currently supporting an application that runs in SQL
Server 2000. One of our customers is having a problem
with filtering items from the database using dates. The
problem is that they can only filter on dates using US
date format, ie mm/dd/yy, yet once the day exceeds 12 they
can no longer view any data from the database. So if they
filter on 09/12/03 that will work fine and pull back data
from the 12th of September. If they filter on 09/13/03 it
does not pull anything back, even though there is data
from that day. Using UK format dates does not pull any
data back at all. This does not happen for any of my
other customers who are running the same application in
SQL Server 2000. Is there an option that can be set
either on the database or on the server that can affect
this?
Any ideas would be greatly appreciated.Ideally this requires a code change in your application.
Your app needs to pass dates around in a non ambiguous
format eg :- '13 Mar 2003', this format can cause problems
if you are running multiple languages though. The safeest
bet is yyyymmdd SQL will always interpret this as ymd.
If a code change isn't possible and you want a quick fix
for this client, compare the regional settings in control
panel to the troublesome machine to a known good one. I
imagine it's running English (US) and everyone else is
using English (UK) or vice versa.
HTH
Ryan
>--Original Message--
>I am currently supporting an application that runs in SQL
>Server 2000. One of our customers is having a problem
>with filtering items from the database using dates. The
>problem is that they can only filter on dates using US
>date format, ie mm/dd/yy, yet once the day exceeds 12
they
>can no longer view any data from the database. So if
they
>filter on 09/12/03 that will work fine and pull back data
>from the 12th of September. If they filter on 09/13/03
it
>does not pull anything back, even though there is data
>from that day. Using UK format dates does not pull any
>data back at all. This does not happen for any of my
>other customers who are running the same application in
>SQL Server 2000. Is there an option that can be set
>either on the database or on the server that can affect
>this?
>Any ideas would be greatly appreciated.
>.
>