June 3, 2010 at 8:34 pm
Hi Forum, I modified following script from last12month. Supposed it flags last 60 days, but it starts flagging first day of last second month. Could you help me to modify it to work properly, thanks.
DECLARE @YestCurrentDate DATETIME
DECLARE @Last2MonthDate DATETIME
DECLARE @FromLast2Month INT
SET @YestCurrentDate = DATEADD(dd,-1,GETDATE())
SET @Last2MonthDate = DATEADD(dd,-60,@YestCurrentDate)
SET @FromLast2Month = substring(convert(varchar,@Last2MonthDate,112),1,6)+'01'
BEGIN
UPDATE dbo.DimDateTime
SET Last2Month = 1
WHERE DateID >= @FromLast2Month and DateID <= convert(varchar,@YestCurrentDate,112)
END
June 3, 2010 at 9:47 pm
I think the last part of the where clause needs to be <, not <=.
Also, you might want to look at the "Common Date/Time Routines" link in my signature for more efficient ways of doing date manipulations.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 3, 2010 at 10:56 pm
That didn't help Wayne, just starts flagging second day of the month. In your note all of them are giving first day of month, week... What I need here, is last 60 days only. For ex. today 5 June, it has to flag the data starting from 5 april.
Anyway, thanks for response.
June 4, 2010 at 1:30 am
Can you confirm the Data Type of the field DateID?
Abhijit - http://abhijitmore.wordpress.com
June 4, 2010 at 2:07 am
It's int, Abhijit.
June 4, 2010 at 2:20 am
Change
SET @FromLast2Month = substring(convert(varchar,@Last2MonthDate,112),1,6)+'01'
to
SET @FromLast2Month = substring(convert(varchar,@Last2MonthDate,112),1,8)
That should do the trick
June 4, 2010 at 2:23 am
For ex. today 5 June, it has to flag the data starting from 5 april
Dehqon, from this requirement, i see that you are interested in the same day of the previous 2nd month..so why dont you change DATEADD(dd,-60,@date) to DATEADD(mm,-2,@date) ?
June 4, 2010 at 2:33 am
ColdCoffee, the OP said 60 days, not 2 months, right?
June 4, 2010 at 2:59 am
ColdCoffee, the OP said 60 days, not 2 months, right?
Initially, the OP said it is 60 days, but if u could see the quote i my previous post, the OP has said the if the date is 5 june, then all he needs is data from 5 april! Thats where i got confused and had put that code.. probably lets wait for the OP to post back..
June 4, 2010 at 3:29 am
Thanks to Jan Van der Eecken, it's working exactly as I wanted :-):-)
ColdCoffee, that one i tried already, that flags the first day of last 2nd month also. If it had flagged to the same day of last 2nd month, it would be ok for me as well.
Thanks to All.
June 4, 2010 at 3:35 am
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply