Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrm23
Posting Yak Master
198 Posts |
 Posted - 2008-08-06 : 02:20:25
|
| Hi,I have a date parameter @from_date in my report.I have to fetch the data based on the date entered for CURRENT and LAST months.I have used :pt.est_start_date between dateadd(mm,-1,@from_date) and @from_date) -- for CURRENT month and pt.est_start_date between dateadd(mm,-2,@from_date) and dateadd (mm, -1 , @from_date) -- for LAST month.This works fine for few dates, but when it comes to LAST DAY of the month, say 30th or 31st, the previous month's date is incorrect.For instance, if the user enters 1-Jul-2008 as the date,then Current month = 2-Jun-2008 to 1-Jul-2008Last month = 2-May-2008 to 1-Jul-2008But if it is last day of the month, it is giving error.Please help as it is very urgent.Thanks in advance |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-06 : 03:01:57
|
| your query is looking for 'this' day last month. so when it's the 31st July it looks for 31st June which obviously does not exist, hence your error. what would you like it to look for instead?Em |
 |
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2008-08-06 : 03:05:55
|
| hi,if that is the case, then it should take 1st July into considerationThanks |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-08-06 : 03:20:00
|
| is this really the requirement? maybe it's just me but it seems flawed to me. you're comparing different periods everytime. no 2 months would be same, surely? would it not be better to just take calender months? or a rolling 30 days? anyway... to do what you want will no doubt mean building a case statement to decide in which months you are taking 'this day last month' and which ones will be '1st day on next month'.Em |
|
|
|
mrm23
Posting Yak Master
198 Posts |
Posted - 2008-08-06 : 03:56:24
|
| Hi elancaster,thanks for the reply.You are correct, the total no.of days in each month varies. So, i we take 30 days as fixed range,it still gives problem as the date remains the same.As i said, if it is 10th July, then the range will be upto 11th June.So, the month part decreases by 1 and date increases by 1.But, if the date is 31, then it should consider 1st of the same month.Now i am trying something like this: select dateadd(day,-30,@from_date)now in this i want to add 1 to the day....Pls give me some suggestion |
|
|
|
Narender
Starting Member
1 Post |
Posted - 2008-10-31 : 06:19:36
|
| Hi,DECLARE @PreviousMonthDate VARCHAR(12)SET @PreviousMonthDate = ( SELECT CAST(DATEPART(MM,DATEADD(MM,-1, CAST('1/31/2008' AS DATETIME))) AS VARCHAR) +'/01/'+ CAST(DATEPART(YY,DATEADD(MM,-1, CAST('1/31/2008' AS DATETIME))) AS VARCHAR) )SELECT CAST(@PreviousMonthDate AS DATETIME)Hope it'll work.RegardsNarender.kaushik@siliconbiztech.com |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-31 : 12:11:39
|
quote:
Originally posted by Narender
Hi,DECLARE @PreviousMonthDate VARCHAR(12)SET @PreviousMonthDate = ( SELECT CAST(DATEPART(MM,DATEADD(MM,-1, CAST('1/31/2008' AS DATETIME))) AS VARCHAR) +'/01/'+ CAST(DATEPART(YY,DATEADD(MM,-1, CAST('1/31/2008' AS DATETIME))) AS VARCHAR) )SELECT CAST(@PreviousMonthDate AS DATETIME)Hope it'll work.RegardsNarender.kaushik@siliconbiztech.com
why all this casting? just use the belowpt.est_start_date between dateadd(mm,datediff(mm,0,@from_date),0) and @from_date for start of month till todayandpt.est_start_date between dateadd(mm,datediff(mm,0,@from_date)-1,0) and dateadd(mm,datediff(mm,0,@from_date),0)for last monthsand just in case what you want is rolling 30 days period then use belowpt.est_start_date between dateadd(dd,datediff(dd,0,@from_date)-30,0) and @from_date for currentand pt.est_start_date between dateadd(dd,datediff(dd,0,@from_date)-60,0) and dateadd(dd,datediff(dd,0,@from_date)-30,0) for previous |
|
|
|
|
|
|
|
|
VUrgent- How to calculate date for previous month?