469,964 Members | 1,826 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,964 developers. It's quick & easy.

SQL Where Clause Date Parameter

benchpolo
142 100+
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.
Oct 22 '07 #1
4 10161
amitpatel66
2,367 Expert 2GB
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..
Oct 23 '07 #2
benchpolo
142 100+
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.
Oct 23 '07 #3
benchpolo
142 100+
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.
Oct 23 '07 #4
benchpolo
142 100+
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)
Oct 23 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Brian Shannon | last post: by
7 posts views Thread by Britney | last post: by
9 posts views Thread by =?Utf-8?B?VHlsZXIgUy4=?= | last post: by
1 post views Thread by rainxy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.