April 15, 2012 at 1:46 pm
hbtkp (4/15/2012)
i am already have training in sql, this is somehow tricky ,that dates is not working after feb
No, it's not at all tricky. You take the code I posted and you use it. It shows you how to increment by month and get the last day of the month. All there, copy it into management studio and run it unchanged and you should see that it does work. Between the last two pieces of code I posted you should also be able to figure out how to use it in your 'match function' loop.
As for finding the first day of the month:
http://sqlinthewild.co.za/index.php/2007/11/05/datetime-manipulation/
p.s. You need training. This stuff we're talking about is very basic T-SQL and you're struggling with it. Hence you need training or help. Speak to your colleagues, speak to your boss.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 1:47 pm
In case you missed it. This is a loop that produces (see the PRINT) the last day of each month, it does so correctly for all months, including after February.
GilaMonster (4/15/2012)
Because this:
DECLARE @row1 datetime, @Todate DATETIMEset @row1 = '2010/02/01' -- however it gets assigned,
set @Todate = '2011/02/01' -- however it gets assigned,
while(@row1 <= @Todate)
BEGIN
PRINT CONVERT(VARCHAR(28), DATEADD(dd,-1,@row1), 121)
set @row1 = DATEADD(MONTH , 1 , @row1)
end
gives this as the DATEADD(dd,-1,@row1) values
2010-01-31 00:00:00.000
2010-02-28 00:00:00.000
2010-03-31 00:00:00.000
2010-04-30 00:00:00.000
2010-05-31 00:00:00.000
2010-06-30 00:00:00.000
2010-07-31 00:00:00.000
2010-08-31 00:00:00.000
2010-09-30 00:00:00.000
2010-10-31 00:00:00.000
2010-11-30 00:00:00.000
2010-12-31 00:00:00.000
2011-01-31 00:00:00.000
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 1:57 pm
i havent missed anything.
can you please rutn this code,you will know
DECLARE @row1 datetime, @Todate DATETIME
set @row1 = '2010/09/30' -- however it gets assigned,
set @Todate = '2011/12/31' -- however it gets assigned,
while(@row1 <= @Todate)
BEGIN
PRINT CONVERT(VARCHAR(28), DATEADD(dd,-1,@row1), 121)
set @row1 = DATEADD(MONTH , 1 , @row1)
end
my @row1 will be like this date ,if you run this one ,it wont work
run exactly what i have posted
April 15, 2012 at 2:00 pm
hbtkp (4/15/2012)
i havent missed anything.
Yes, you have. You've missed the fact that the @row needs to be set to the FIRST DAY OF THE MONTH.
I've said that three times now. FIRST DAY OF THE MONTH, not the last day, as it is in the code that I have posted more than once. If you're not going to read what I write, I'm outa here.
For the 4th time...
DECLARE @row1 datetime, @Todate DATETIME
set @row1 = '2010/02/01' -- FIRST DAY OF THE MONTH!!!!!!!!!!!!!!!!!
set @Todate = '2011/02/01' -- however it gets assigned,
while(@row1 <= @Todate)
BEGIN
PRINT CONVERT(VARCHAR(28), DATEADD(dd,-1,@row1), 121)
set @row1 = DATEADD(MONTH , 1 , @row1)
end
run exactly what i have posted
How about you run exactly what I've posted and see that it really does work. As in exactly what I posted, without any modifications including to the start date. And the link I posted shows you how to get the first day of the month if any date is given.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 2:10 pm
i cant set @row1 first day of the month,it can be any date. its not hard coded,user will enter it and it could be any day
April 15, 2012 at 2:17 pm
GilaMonster (4/15/2012)
And the link I posted shows you how to get the first day of the month if any date is given.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 2:25 pm
finally got it ,with this code
DECLARE @row1 datetime, @Todate DATETIME
set @row1 = '2010/09/30' -- however it gets assigned,
set @Todate = '2011/12/31' -- however it gets assigned,
while(@row1 <= @Todate)
BEGIN
PRINT DATEADD(dd,-DAY(DATEADD(m,1,@row1)),DATEADD(m,1,@row1))
set @row1 = DATEADD(MONTH , 1 , @row1)
end
April 15, 2012 at 2:32 pm
hbtkp (4/15/2012)
Lynn,i am already have training in sql, this is somehow tricky ,that dates is not working after feb
Nothing you have been given is tricky. It is all basic t-sql. You need more training. You can't. Even debug basic code or code logic.
April 15, 2012 at 5:53 pm
ok. 1 more prob.
my function from which i need to fetch data @Reportdata3 , has fromdate and todate
so when i execute my sp exec dashboard
i am supplying fromdate and todate
so item3 is taking that date and generate data.
now i need to update search for @row1 date is there into fromdate, but it just gives me whatever i am supplying.
i need to update fromdate = @row1 in loop.if i put in where condition,it doesnt give me date
how to update in udf
April 16, 2012 at 10:33 am
i've nothing to add to this post but had to respond...
Gila and others, you have amazing patience and your work with the community on this website deserves much thanks.
For people to come here and ask questions (good or bad) and get the level of help you give is nothing short of outstanding.
I'm not sure i saw a single thank you from the op, so i figured i'd give it 🙂
April 16, 2012 at 10:18 pm
April 17, 2012 at 8:35 am
i hv this formula for cumulative
(exp(sum(LOG(CASE WHEN I1 = 0.0 THEN 1 WHEN I1 IS NULL THEN 1 WHEN I1= -100 THEN 1 ELSE 1+I1/100 END)))/-1)* 100,
i am getting error msg like
Msg 8115, Level 16, State 2,
Arithmetic overflow error converting expression to data type float.
April 17, 2012 at 8:53 am
hbtkp (4/17/2012)
Arithmetic overflow error converting expression to data type float.
Use Google and search for that exact message. You will get lots of articles that will help you troubleshoot the issue.
Viewing 13 posts - 76 through 87 (of 87 total)
You must be logged in to reply to this topic. Login to reply