|
Damn!
I need a coffee. Or actually rather a beer. Or both.
|
|
|
|
|
a.code = b.code(+) The (+) says any a.code value will match a NULL b.code value. Meaning you get all the a.code values.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Hi
I have written a stored procedure where i am taking the select query into a variable @string and executing the query.
i am generating the query dynamically based on the SP inputs. If the query is below 4000 chars , there is no prob with it. If it exceeds then query is not executing. Kindly help....
Thanks in advance
Naina
Naina
|
|
|
|
|
If I'm right you will not be able to query/search for data/text that is larger than 4000 chars.
All column types above text(4000), like NVarChar(max) can't be a query parameter.
I hope somebody can confirm that.
Greetings
Covean
|
|
|
|
|
Hi thanks for the reply..... just did a small change by declaring as varchar(MAX) and its accepting more than 4000. Thank you
Regards
Naina
Naina
|
|
|
|
|
No problem and thank you for confirming that I'm wrong.
And good to know. 
Greetings
Covean
|
|
|
|
|
You may be limited to 8000 character, the max of varchar, not sure if varchar(max) overcomes this limitation.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
if i give nvarchar(8000) it says that i have crosses the linit of 4000 characters. i am using sqlserver 2005. and now i noticed that varchar(MAX) is taking only 4000 characters.
Regards
Naina
Naina
|
|
|
|
|
use varchar(8000)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i declared as varchar(8000) and assigned a query to it.
but when i tried to print the length and string, it shows only till 4000 chars and the length also its showing only 4000. 
Regards
Naina
Naina
|
|
|
|
|
Hi
use nvarchar(8000)
umesh
|
|
|
|
|
The size (8000) given to the parameter '@sql' exceeds the maximum allowed (4000). is the error if i use nvarchar(8000)
Regards
Naina
Naina
|
|
|
|
|
Yes... Hightest value of NVARCHAR is 4000 and VARCHAR is 8000
BUT NVARCHAR takes large CHARSET.. So use NVARCHAR only when you need. 
|
|
|
|
|
I finally got the answere.....
I declared @string as varchar(8000). But there are nvarchars declared which i am assigning to @string which finally lead to the problem . because of this @string is taking only 4000 charecters, I replaced all nvarchars with varchars and finally its taking morethan 4000 charecters.
Regards
Naina
Naina
|
|
|
|
|
Take @string as varchar(max) and then try executing it
Hope it helps!
|
|
|
|
|
Hi,
I am struggling to get the week number and week day of the particular day. I know we can use Datepart function which is available in sql server 2008 to display the week number of the given date. but my situation is different. for example
Datepart(wk,'2009-12-30') gives 53 weeks
Datepart(wk,'2010-01-02') gives 1. but I wanted to display it as 54.
whenever the year is changing I need to add these week number to the previous year one.
In the below table Bugdate is the input, and weeknumber and startday of the week are the expected outputs.
BugDate ----- weeknumber ---- Startday of the Week
....
12/13/2008---- 50------------- 12/7/2008
12/14/2008---- 51------------- 12/14/2008
12/21/2008---- 52------------- 12/21/2008
12/23/2008---- 52------------- 12/21/2008
12/30/2008---- 53------------- 12/28/2008
1/2/2009------- 54------------- 12/28/2008
1/6/2009------- 55------------- 1/4/2009
...
12/20/2009---- 105------------ 12/20/2009
12/28/2009---- 106------------ 12/27/2009
1/1/2010------ 106------------ 12/27/2009
Can any one help me to solve this problem.
Thanks in advance.
|
|
|
|
|
You could do something like
Select Datepart(wk,'2010-01-02') + ((Datepart(yr,'2010-01-02') - 2009) * 52
OR get the datediff days / 7 and use floor or ceiling to get the number you want (this is more reliable as the week numbers per year do not change.
This is a fairly simple exercise of thinking around corners, you are not flexing you brain enough.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
Thanks for your reply to get the week number. But how to get the first day of the week for the given date?
Thanks,
|
|
|
|
|
Read up on datepart in BOL, there is a dayofweek element that will tell you.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
the query is failing in one situation. eg, when dates are '2011-12-31'
'2012-01-01', this case returning same week number for both the dates but this is wrong. For '2012-01-01' this is the start of the week so it is increment of the previous date week...
Date-------- weekNumber
2011-12-31-- 157
2012-01-01-- 157(wrong result it should be 158)
2012-01-02-- 157(wrong result it should be 158)
Thanks..
|
|
|
|
|
But as per the query which I have given it should be
Date-------- weekNumber
2011-12-31-- 212 and not 157
2012-01-01-- 213 and not 157 or 158
2012-01-02-- 213 and not 157 or 158
And it is correct only. Even I calculated the value.
Only change the case statement by the following lines
case when Datepart(yy,bugdate) = 2009 then Datepart(wk,bugdate) + 53*1
when Datepart(yy,bugdate) = 2010 then Datepart(wk,bugdate) + 53*2
when Datepart(yy,bugdate) = 2011 then Datepart(wk,bugdate) + 53*3
when Datepart(yy,bugdate) = 2012 then Datepart(wk,bugdate) + 53*4
else Datepart(wk,bugdate)
end as weeknumber
Niladri Biswas
|
|
|
|
|
the problem with your query is if the years will be increased in the future. so instead of adding condition many times we need to summarize that. second
is
--Date----- weeknumber
2008-12-30--- 53
2009-01-02--- 54( this is wrong it should be 53, it falls in the 53rd week)
Thanks,
|
|
|
|
|
Come on. it is correct. Because if you execute
select Datepart(wk,'12/30/2008') you will get the weeknumber as 53 and
select Datepart(wk,'1/2/2009') will yield 1.
As per your requirement, which you specified it should be 54. and henceforth the result.
For the first part(if the years will be increased in the future. so instead of adding condition many times we need to summarize that) we need to work on.
Niladri Biswas
|
|
|
|
|
sorry,I am wrong, the out should be 53 not 54. because 1/2/2009 falls in week 53. Is it possible to modify the query to meet my requirement...
The reply given by 'Mycroft Holmes' is working perfect but there is a small problem in that query, I posted the same in earlier thread.
Thanks,
|
|
|
|
|
Please provide a sample output which should be correct enough. Otherwise it is becoming difficult for me to understand ur requirement.
Also u give a shot. As I told u that u can learn better if u try by urself.
Niladri Biswas
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
