Job to be run ever first for the previous calendar month

Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.tolcis,

There might be a more elegant way, but this should work:

declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime

set @.Now = getdate()
if datepart(d, @.Now) = 1 -- only execute if it is the first day of the month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @.StartDate = convert(varchar(15), dateadd (m, -1, @.now), 112)
-- to get end date, remove time from date
set @.EndDate = dateadd(m, 1, @.StartDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate
end

-- Bill

"tolcis" <nytollydba@.gmail.comwrote in message
news:1169073028.098703.235980@.11g2000cwr.googlegro ups.com...

Quote:

Originally Posted by

Hi!
>
I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?
>
Thank you,
T.
>

|||WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)

Roy Harvey
Beacon Falls, CT

On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:

Quote:

Originally Posted by

>Hi!
>
>I have a query that has to return bunch of data based on the calendar
>month. I have to make sure that it will return data to me for 28 days
>if it is February and for 31 if it is August(for example). I need to
>be able to execute it every first of every month for the past 30, 31 or
>28 days based on the calendar month. Is there a function or a stored
>procedure that I can use to do that?
>
>Thank you,
>T.

|||tocis,

Ignore the first post, this is better. Schedule your job to be run on the
first of every month. This will give you the prior month date range for any
date, not just the 1st. This way if the job fails you can also run it on the
2nd, 3rd, etc.

declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime

set @.Now = getdate()

-- to get end date, subtract days to get to get 1st of the month for any
date
set @.EndDate = dateadd(d, 1 - datepart(d,@.Now), @.Now)

-- remove the time portion
set @.EndDate = convert(char(12), @.EndDate, 112)

-- subtract a month for start date
set @.StartDate = dateadd(m, -1, @.EndDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate

-- Bill

"AlterEgo" <alterego55@.dslextreme.comwrote in message
news:12qte0qjqivt0ae@.corp.supernews.com...

Quote:

Originally Posted by

tolcis,
>
There might be a more elegant way, but this should work:
>
declare @.Now datetime
declare @.StartDate datetime
declare @.EndDate datetime
>
set @.Now = getdate()
if datepart(d, @.Now) = 1 -- only execute if it is the first day of the
month
begin
-- to get start date, subtract one month from the date and remove the
time from the date
set @.StartDate = convert(varchar(15), dateadd (m, -1, @.now), 112)
-- to get end date, remove time from date
set @.EndDate = dateadd(m, 1, @.StartDate)
print cast(@.StartDate as char(25)) + cast(@.EndDate as char(25))
-- execute your code here using >= @.StartDate and < @.EndDate
end
>
-- Bill
>
"tolcis" <nytollydba@.gmail.comwrote in message
news:1169073028.098703.235980@.11g2000cwr.googlegro ups.com...

Quote:

Originally Posted by

>Hi!
>>
>I have a query that has to return bunch of data based on the calendar
>month. I have to make sure that it will return data to me for 28 days
>if it is February and for 31 if it is August(for example). I need to
>be able to execute it every first of every month for the past 30, 31 or
>28 days based on the calendar month. Is there a function or a stored
>procedure that I can use to do that?
>>
>Thank you,
>T.
>>

>
>

|||Roy,

Wya cool, did not know this!

-- Bill

"Roy Harvey" <roy_harvey@.snet.netwrote in message
news:vietq2l4344pq52plgrnael566amuu5pfb@.4ax.com...

Quote:

Originally Posted by

WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:
>

Quote:

Originally Posted by

>>Hi!
>>
>>I have a query that has to return bunch of data based on the calendar
>>month. I have to make sure that it will return data to me for 28 days
>>if it is February and for 31 if it is August(for example). I need to
>>be able to execute it every first of every month for the past 30, 31 or
>>28 days based on the calendar month. Is there a function or a stored
>>procedure that I can use to do that?
>>
>>Thank you,
>>T.

|||Thanks. Works great.

Roy Harvey wrote:

Quote:

Originally Posted by

WHERE create_date < DATEADD(month,DATEDIFF(month, 0,getdate()),0)
AND create_date >= DATEADD(month,DATEDIFF(month, 0,getdate())-1,0)
>
Roy Harvey
Beacon Falls, CT
>
On 17 Jan 2007 14:30:29 -0800, "tolcis" <nytollydba@.gmail.comwrote:
>

Quote:

Originally Posted by

Hi!

I have a query that has to return bunch of data based on the calendar
month. I have to make sure that it will return data to me for 28 days
if it is February and for 31 if it is August(for example). I need to
be able to execute it every first of every month for the past 30, 31 or
28 days based on the calendar month. Is there a function or a stored
procedure that I can use to do that?

Thank you,
T.

sql

JET DB initilization problem help!!!

Hi I have set up a access database on a doc library and a infopath form based on this db published to a nearby folder. When I go to fill out the form, and it opens, I hit submit. But then it gives me a JET DB initilization form with login of admin and a blank pass. When I hit ok, it gives me a error saying

infopath The Microsoft Jet database engine cannot open the file '\\207.245.47.37\sites\Safety_and_Training\Experimental\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.

For some reason, once or twice its randomly been able to submit succcessfully but I havnt been able to reproduce the situation. Are there files that i am missing? any permissions that I need to check? The access database only has a admin and no password account. PLEASE HELP ME ive been working on this for ages! If you know a solid workaround with little programming let me know.

Hi,

This is a SQL Server forum, i suggest you direct your question to an Access specific forum where you should find someone able to help.

Cheers,

Leigh

JDBC driver for Microsoft SQL Server CE / Compact Edition

Hi,

I have a J2EE based web application. There is a requirement where user triggers a process to query a database, and the application needs to save the results to an SDF file (Microsoft SQL Server CE 2.0 / Microsoft SQL Server Compact Edition) database. I need JDBC driver for it, but could not find it anywhere. Please help. Also, if there is any alternate way to do this, please let me know.

Thanks,
Vaibhavhttp://technet.microsoft.com/en-us/sqlserver/bb331716.aspx & http://technet.microsoft.com/en-us/sqlserver/bb331729.aspx should get you what you want.|||No it does not. I think there is no such thing developed yet.|||You could submit such request by using http://connect.microsoft.com and also search under http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html#996747 Sun website for any relevant ODBC-JDBC bridge.