How to insert values for prior month stat date and end date 2

[awaria]

I have the query below that I want to schedule to run on the 20th of every month, where the dates for the query should be the start date and the end date of the prior month.

If run on 10/20/2011, start date = 9/01/2011 end date = 09/30/2011.

Is there a way to accomplish this so I can schedule this job and know it will pull data from the desired date range each month?

---------
select * from Table
where [Posted Date] between '2011-09-01 00:00:00.000' and '2011-09-30 00:00:00.000'
---------

Thanks,

Andrew

 

[markros]

declare @EndDate datetime, @StartDate datetime, @RunDate datetime

set @RunDate = '20111020'

set @EndDate = dateadd(month, datediff(month, '19000101', @RunDate),'19000101') -- use this date as last date and instead of between use >= and <

set @StartDate = dateadd(month, -1, @EndDate)

select @RunDate, @StartDate, @EndDate

PluralSight Learning Library

 

[awaria]

Markos,

Thanks so much.

Works great.

Is there a way to set @Rundate to today, some SQL function, in this example 10/20/2011, so each month it will run without requiring any data entry changes on my part?

Thanks again for your help and expertise.

Andrew

 

[awaria]

I think I found it, I believe it's GETDATE().


Thanks again,

Andrew

 

[markros]

Getdate() or CURRENT_TIMESTAMP in SQL Server.

PluralSight Learning Library

 

[awaria]

I have one final question, which is, what is the function to "convert" the date fields to a format of YYYYMMDD, instead of rendering as 09/01/2011? I cannot locate any articles on how to do this, or articles that I can comprehend the examples.

Andrew

 

[gmmastros]

You can use the Convert function with the (optional) 3rd parameter which is for style.

Ex:

Select Convert(VarChar(8), GetDate(), 112)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

Thank you very much.

I could not get that. I was working with the 112 format, but couldn't apply the appropriate syntax.

Thanks again

 

[awaria]

I want to now run this query monthly, have the results save to a text file (csv), where the file name is named for the month and year pertaining to the data selected in the query. Such as the query run on Oct 20 will be for for Sep 2011 data, so that month's file name would be 0911.csv.

I have been testing with DTS and a straight SQL job, but, without success. Due to my lack of expertise I am sure.

What are your recommendations for methods for accomplishing this?

Thanks,

Andrew

 

[awaria]

Update, I can create a DTS that will generate the desired data, every month, save it to a .csv file, however, the file has a constant name, or the same file name for every month.

Not sure how to get past this obstacle.

AW

 

[drlex]

I get over this issue by having a blank template .csv file and copy it prior to the output and then move the output file to the live area.
e.g.

SET @Command =  'copy "D:\Data\Blank.csv"  "D:\Data\Sales.csv"'
USE master
EXEC  xp_cmdshell @command

[i]...do extract into Sales.csv[/i]

SET @Command = 'copy "D:\Data\Sales.csv"  "\\Acmefile01\Cognos\Output\Sales_' + CONVERT(CHAR(8), GETDATE(), 112) + '.csv' + '"'
USE master
EXEC  xp_cmdshell @command

soi là, soi carré

 

[awaria]

Thanks for your post.
I'm trying to replicate, but getting an error.
Can you audit my code/syntax and see if you can find the issue.
I get the messsage "'c' is not a recognized internal or external command"
-------------
DECLARE @Command VARCHAR
SET @Command = 'copy "\\FHGP1\finance\andrew\medassets\AriaHealth_CRF_01_XXXX_AW.csv" "\\FHGP1\finance\andrew\medassets\AriaHealth_CRF_01_YYYY_AW.csv"'
USE master
EXEC xp_cmdshell @command

--do extract into Sales.csv

DECLARE @Command VARCHAR
SET @Command = 'copy "\\FHGP1\finance\andrew\medassets\AriaHealth_CRF_01_YYYY_AW.csv" "\\FHGP1\finance\andrew\medassets\AriaHealth_CRF_01_' + CONVERT(CHAR(8), GETDATE(), 112) + '_AW.csv' + '"'
USE master
EXEC xp_cmdshell @command
---------------------------------
Thanks,

AW

 

[drlex]

Sure - you haven't specified a length for the variable @COMMAND, so it's a default 1 character length. Thus you're asking xp_cmdshell to execute the instruction 'c'.
Declare it as varchar(400).

soi là, soi carré