April 14, 2012 at 11:06 pm
I give up. You need to figure out how to do basic trouble shooting.
April 15, 2012 at 5:51 am
hbtkp (4/14/2012)
they both are datetime.how to compare two dates with <=
Oddly enough, I showed you in an earlier piece of code...
IF (@Date1 <= @Date2)
Simple as that.
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 8:32 am
ok the condition is working,
but i have problem with if(stat)
whats wrong in that
April 15, 2012 at 10:07 am
To hbtkp,
You probably don't want to here this but how about learning some SQL before asking increasingly frustrating questions.
Try this...
Introduction to SQL 2008
http://www.microsoft.com/learning/en/us/course.aspx?id=2778a
If your boss thinks you know SQL already then he is wrong.
Reading the forum posts...you don't seem to understand ddl, tables, functions, date datatype, how to ask for help correctly.
Given create statements for tables, some sample (made up non confidential) data, and expected results then you may get help.
Fitz
[/left]
April 15, 2012 at 10:53 am
hbtkp (4/15/2012)
ok the condition is working,but i have problem with if(stat)
whats wrong in that
Without a lot more details and sample data, no idea. Start by looking at the values of the variables and what's returned from the function and you might be able to see what's wrong.
That said, I think you need to sit down with a colleague who know T-SQL and get some help from someone who can see what's going on. That or ask your boss for a basic 'introduction to T-SQL' course.
We could spend weeks on this and it's getting pretty clear that you need some basic knowledge that at this point you don't have.
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 12:15 pm
i know basic,
i am just trying to get idea.
the prob is
set @row1 = DATEADD(MONTH , 1 , @row1)
so if i have date @row1 = 01/31/2010
next @row1 would be 02/28/2010
and next would be 03/28/2010(which is wrong)
how add exact month
so that i can get 03/31/2010
next 04/30/2010
April 15, 2012 at 12:28 pm
Easier to dateadd the 1st of the month, then subtract a day.
So, rather set @row1 = 2010/02/01 (btw, use the yyyymmdd format as the others are ambiguous)
then dateadd will always produce the next 1st day of the month. You can subtract a day to get the last day of the previous month when you check the functions
So, roughly
set @row1 = 2010/02/01 -- however it gets assigned,
while(@row1 <= @Todate)
begin
if ( DATEADD(dd,-1,@row1) not in(select fromdate from item2(@ReportData1)))
insert into #temp4
select I1 from item3(@Reportdata3) where fromdate = DATEADD(dd,-1,@row1)
set @row1 = DATEADD(MONTH , 1 , @row1)
end
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 12:42 pm
this is not working
i am getting date liek this
2010-10-31 00:00:00.000
2010-11-30 00:00:00.000
2010-12-30 00:00:00.000
2011-01-30 00:00:00.000
2011-02-28 00:00:00.000
2011-03-28 00:00:00.000
its just adding month, not the exact day ,i tried your code,still not getting
April 15, 2012 at 12:52 pm
Then you have changed the code I gave you and broke it somehow.
Because this:
DECLARE @row1 datetime, @Todate DATETIME
set @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
I will suggest again, sit with a colleague who knows SQL or ask your boss for a SQL course.
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:18 pm
nope ,i have chnage anything
the prob is month of feb,is you try this
DECLARE @row1 datetime, @Todate DATETIME
set @row1 = '2010/02/28' -- 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
you will know after feb its not incremeting
April 15, 2012 at 1:25 pm
hbtkp (4/15/2012)
nope ,i have chnage anything
Really?????? You didn't change anything?
Your code:
the prob is month of feb,is you try this
DECLARE @row1 datetime, @Todate DATETIME
set @row1 = '2010/02/28'-- however it gets assigned,
set @Todate = '2011/02/01' -- however it gets assigned,
My code:
DECLARE @row1 datetime, @Todate DATETIME
set @row1 = '2010/02/01' -- however it gets assigned,
set @Todate = '2011/02/01' -- however it gets assigned,
In bold is a difference. Hence, you changed something and it broke as a result.
I said to set the date to the FIRST day of the month. You went and set it to the last and claim that my code (which you changed) doesn't work. My code does, your modification does not. Set the date to the FIRST DAY OF THE MONTH and use DATEADD to get the last day, then the month increment will work correctly, as I showed with the results I posted from my code.
If you're not willing to read and follow simple advice, I'm done. Sit with a colleague who knows T-SQL or go to your boss and ask for help.
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:28 pm
i knwo tits working for first day of month.
but my req is if i enter any date in a month, i should get next month first day
April 15, 2012 at 1:33 pm
usually i need t put month last day ,not first day
for example.
9/30/2010
can be my row1,so after coming to feb it just adding month ,not days
April 15, 2012 at 1:40 pm
Right now I am in a car heading east on I40 heading toward New Mexico. I am not even going to try giving you any code. I'd tell you to follow the link to my blog site and look for the post regarding common date functions, but you probably wouldn't be able to figure out how to even use them.
Sit with a coworker or go tell your boss that you need more training in T-SQL and Microsoft SQL Server.
April 15, 2012 at 1:42 pm
Lynn,
i am already have training in sql, this is somehow tricky ,that dates is not working after feb
Viewing 15 posts - 61 through 75 (of 87 total)
You must be logged in to reply to this topic. Login to reply