Friday, 30 September 2005

When two things are the same but not!!

Just wasted a few hours on a problem with parameters in SQL Reporting Services.

We have a report that take a date as one of the parameters. The list of values for this parameter is provided by a stored procedure that removes the time portion of the date and shows the date in the end-user recognisable format of dd MMM yyyy.

Now we also render this report via the web service in a regular batch using a little C# app that is quickly becoming my little pet hate. The list of recipients and parameters is provided by another stored procedure that puts the date in the same format. Matching the format is important because when you have a list of values for a parameter, the value you send via the web service has to match one of the values in the list or you get a error saying it's not a valid value.

Compare the following and tell me if you can detect the difference.

CONVERT(varchar(12), GetDate(), 106) as Date_106
, CONVERT(varchar(12), GetDate(), 113) as Date_113

Date_106 Date_113
------------ ------------
30 Sep 2005 30 Sep 2005

The difference surfaces when you use DATALENGTH to determine how many bytes are used. Converting to varchar(12) using date style 106 is 11 bytes, while date style 113 is 12 bytes. No trimming of the trailing whitespace.

Feeling just a slight bit of frustration ... why can't spaces be more visible [:$]

No comments: