ANSI PADDING | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

ANSI PADDING

Hello there,<br /> We’ve upgraded a database from SQL 6.5 to 2000 and the following is an example of a problem we’re seeing after the upgrade:<br /><br />declare @tempdate smalldatetime,<br />@pubdate char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />select @tempdate = getdate()<br /><br />SELECT @pubdate = CONVERT(char(1),DATEPART(mm,@tempdate))+’/'<br />select @pubdate, datalength(@pubdate) <br /> <br />SELECT @pubDate = @pubDate + SUBSTRING(CONVERT(char(4),DATEPART(yy,@tempdate)),3,2)<br />select @pubDate<br /><br />The result in 6.5 is 2/04 which is what we want but in 2000 we get 2/.<br /><br />If I change the @pubdate to Varchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> then everything works but we would rather use a setting or a dboption to make it database wide and avoid a lot of changes.<br />Tried using the dboption ANSI PADDING to false and also set ansi_padding off but to no avail. Any reccommendations here or past experiences?<br /><br />Thanks in advance,<br />Patrick<br />
Try<br /><br /><pre><br />declare @tempdate smalldatetime,<br />@pubdate char(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /><br />select @tempdate = getdate()<br /><br />SELECT @pubDate = <br />CONVERT(char(1), DATEPART(mm,@tempdate)) + ‘/’ + <br />SUBSTRING(CONVERT(char(4),DATEPART(yyyy, @tempdate)), 3, 2)<br /><br />select @pubDate<br /></pre><br /><br />Bit confused about your original code…<br /><br /><pre><br />CONVERT(char(1),DATEPART(mm,@tempdate))<br /></pre><br /><br />When month is &gt;= 10, this will overflow the char(1). On SQL 2k this is returned simply as ‘*'<br /><br /><pre><br />SUBSTRING(CONVERT(char(4),DATEPART(yy,@tempdate)),3,2)<br /></pre><br /><br />Youre asking DATEPART to return a 2 digit year, which is then cast as a char(4), which you then substring the last two chars from.<br />Why not just ask DATEPART for a 4 digit year to be safe?<br />
Chappy,
The code is a sample from a stored procedure that was upgraded. It actually does check for the length of the mm data: IF(DATEPART(mm,@tempdate)<10) SELECT @pubdate = ‘0’+CONVERT(char(1),DATEPART(mm,@tempdate))+’/’
ELSE SELECT @pubdate = CONVERT(char(2),DATEPART(mm,@tempdate))+’/’ The reason we don’t pull in the full year is the display on a handheld scanner only has room for a two didgit year. The proc then does the following concatenation:
SELECT @pubDate = @pubDate + SUBSTRING(CONVERT(char(4),DATEPART(yy,@tempdate)),3,2) I could easly make the change to the proc and make it a varchar which trims trailing nulls but I was looking for a setting (if there is one) so that no other procs would pop up with this type of problem. Thanks for your help
Patrick
Very difficult to diagnose this problem, since I cannot get my sql server to return the same results your 6.5 did. <br />I think its unlikely to be a change in behavior between 6.5 and 2k (someone please correct me?!)<br /><br />My first hunch would be that its related to regional settings, in terms of windows.<br />Either way, I still stand by my advice (however unpopular <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> in that you may cause yourself less headaches in the longrun by upgrading the procedures to work robustly.<br /><br />The code in your procs appears to be very poor (no offence, I imagine its not your code!), and so my advice would be to get the avoid looking for a quick fix and spend some valuable time analysing them for improvements
I agree with you. We’ll probably end up fixing each problem as they appear. We have hundreds of procs that go back for years.
Most of the code isn’t mine but comes from various development teams over the years. I can’t guarantee though that my code would be without its faults, I’m definitely not the best coder in the world! Thanks for your advice.

]]>