SQL Where Clause Date Parameter

[benchpolo]

I wrote a SQL script with a rxfilldate (mm/dd/yyyy format) parameter in WHERE clause. Is there way to automate the where clause to pick up the previous months data.

For example: DATEPAID BETWEEN '09/01/2007' AND '09/30/2007'

where rxfilldate,112) BETWEEN
convert(varchar(2),datepart(mm,dateadd(month,-1,getdate()))) + '-01-' +
convert(varchar(4),datepart(yyyy,dateadd(month,-1,getdate())))
AND convert(varchar(20),getdate(),10)

Thanks.

[amitpatel66]

I wrote a SQL script with a rxfilldate (mm/dd/yyyy format) parameter in WHERE clause. Is there way to automate the where clause to pick up the previous months data.

For example: DATEPAID BETWEEN '09/01/2007' AND '09/30/2007'

where rxfilldate,112) BETWEEN
convert(varchar(2),datepart(mm,dateadd(month,-1,getdate()))) + '-01-' +
convert(varchar(4),datepart(yyyy,dateadd(month,-1,getdate())))
AND convert(varchar(20),getdate(),10)

Thanks.

Is your above query not working??
Make use of sysdate and subtract it by one month and get the first day and the last day of that month to get the previous months value..

[benchpolo]

somehow, i am not getting any data.. my guess is because the date format for my rxfilldate is yyyymmdd, and i'm not quite sure how to write the statement based on yyyymmdd format.

[benchpolo]

Below is the actual WHERE CLAUSE im using in my script

CONVERT(varchar(8),pd.rxfilldate,112) BETWEEN
convert(varchar(4),datepart(yyyy,dateadd(month,-1,getdate())))+
convert(varchar(2),datepart(mm,dateadd(month,-1,getdate())))+ '-01-'
AND convert(varchar(20),getdate(),10)

Unfortunately, I am not getting date from last month. Please advise. Thanks.

[benchpolo]

Here's another WHERE CLAUSE statement where the rxfilldate1 format is mm/dd/yy

WHERE convert(varchar(8),pd.rxfilldate1,112) BETWEEN convert(varchar(2),datepart(mm,dateadd(month,-1,getdate()))) + '-01-' +
convert(varchar(4),datepart(yyyy,dateadd(month,-1,getdate())))
AND convert(varchar(20),getdate(),10)