I have a feeling I'm missing something obvious here, but being new to
SQL Server is my only excuse!
I have a table that holds a variety of data. I am writing a user
report where the user wants to be able to return all data that falls
between a start and end date that they enter.
The query should therefore be along the lines of
SELECT [some columns]
FROM [my_table]
WHERE [column holding date] BETWEEN [start date] AND [end date];
However, the column holding the date information has been defined, for
reasons best known to the designer, as a VARCHAR(10) and the data is
in the format dd/mm/yy.
I have tried CAST, which simply gave an error. I then tried CONVERT
in the form CONVERT(datetime, [column holding date], 3) and the query
runs without error but returns no data, even though I can see there is
data that would meet the criteria in the table.
hi all, I have writen a function in which I'm selecting max of varchar datatype column after converting it into integer datatype ,though the where condition of the select statement retrives only numbers which are in string format but I'm getting conversion error for a string value which doesn't satisfy the condition. can any one explain this behaviour? Thanks in advance.
have a SQL server table with a vairbale unitCost of type money and via a stored procedure I am trying to update it. Through asp I capture the value as a string and then ATTEMPT to convert it into a variable of type money before executing the stored procedure as follows.
SqlParameter parameterUnitCost= new SqlParameter("@unitCost", SqlDbType.Money,8); parameterUnitCost.Value = unitCost; myCommand.Parameters.Add(parameterUnitCost);
This gives me an input string error. On the other hand if I change that line to the following, the page works flawlessley:
SqlParameter parameterUnitCost= new SqlParameter("@unitCost", SqlDbType.Money,8); parameterUnitCost.Value = 25.45; myCommand.Parameters.Add(parameterUnitCost);
Hi, Ive imported a data set into SQL. Now on redefining a text field to datetime, most dates that are filled come through okay.
The issue is that there are some empty fields which I'd like for it to stay empty after conversion. Now what happens is that the empty field becomes '01/01/1900' - which is throwing off our queries as we need it to be compared to other date fields.
Is there a way to keep it empty even after the datatype is changed to datetime? Thanks a bunch!
I am trying to convert some strings to number datatype. Some numbers are like this. 1,45.45- 21.21- 4,611.20- Is it possible to convert these type of strings to number datatype. Thank you.
Lets say I have a table named Projects with the data below.Y07001Y07002Y07003Y07010Y07011I want to pick up the last 3 numbers and add a one to it so. Declare @Count as varchar(3) SELECT Top 1 @Count = Right(Project, 3)+1 FROM Projects Order by ID desc SELECT @Count This is what I am using. It picks up the last entry, add a one to it and gives me 12 in return. What i want is 012, not 12
I am trying to insert data from a web form to a SQL Database. I am receiving the following error: {"String was not recognized as a valid Boolean."} I am also receiving a similar error for text boxes that have dates. Below is the code that I am using: <asp:SqlDataSource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:ConnMktProjReq %>" selectcommand="SELECT LoanRepName,Branch,CurrentDate,ReqDueDate,ProofByEmail,ProofByEmail,FaxNumber,ProjectExplanation,PrintQuantity,PDFDisc,PDFEmail,LoanRepEmail FROM MktProjReq" insertcommand="INSERT INTO MktProjReq(LoanRepName, Branch, CurrentDate, ReqDueDate, ProofByEmail, ProofByEmail, FaxNumber, ProjectExplanation, PrintQuantity, PDFDisc, PDFEmail, LoanRepEmail) VALUES (@RepName, @BranchName, @Date, @DueDate, @ByEmail, @ByFax, @Fax, @ProjExp, @PrintQty, @Disc, @Email, @RepEmail)"> <InsertParameters> <asp:FormParameter Name="RepName" FormField="LoanRepNameBox"/> <asp:FormParameter Name="BranchName" FormField="BranchList"/> <asp:FormParameter Name="Date" FormField="CurrentDateBox" Type="DateTime"/> <asp:FormParameter Name="DueDate" FormField="ReqDueDateBox" Type="DateTime"/> <asp:FormParameter Name="ByEmail" FormField="ProofByEmailCheckbox" Type="boolean"/> <asp:FormParameter Name="ByFax" FormField="ProofByFaxCheckbox" Type="boolean"/> <asp:FormParameter Name="Fax" FormField="FaxNumberBox"/> <asp:FormParameter Name="ProjExp" FormField="ProjectExplanationBox"/> <asp:FormParameter Name="PrintQty" FormField="PrintQuantityBox"/> <asp:FormParameter Name="Disc" FormField="PDFByDiscCheckbox" Type="boolean"/><asp:FormParameter Name="Email" FormField="PDFByFaxCheckbox" Type="boolean"/> <asp:FormParameter Name="RepEmail" FormField="LoanRepEmailBox"/> </InsertParameters> </asp:SqlDataSource>protected void Button1_Click(object sender, EventArgs e) { SqlDataSource1.Insert(); } I have been searching forums for parsing data, but I haven't found anything that works. Can anyone provide guidance. Thank you, Paul
I've have about 100 tables, for some reasons, column values that are originally NULL was inserted as emtpy string. So, I am wondering if I can write JUST ONE SQL (hopefully don't have to specify the field names in the SQL as well) for each table so that all the empty strings will be converted back to NULL.
i have a flat file with string column 20060616 . i am converting the string to date (Oracle) i am using the derived column with the following experssion .
while executing the package its failing and throwing the following error
[Derived Column [73]] Error: The "component "Derived Column" (73)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "JOINDDATE" (124)" specifies failure on error. An error occurred on the specified object of the specified component.
I have been trying to read a flat file which has a birthdate field. The field is string and the format is "011594". I have been trying to convert it to a date field in the following format "01/15/1994". Using the forum I was able to write the following expression but I am still keep getting error messages.
I also noticed few records are missing birthdate and few do not have the complete birthdate which means some birthdates are missing year and some are missing month. For example, some are completely missing and some are partially missing, like " " or "0312 ". Could this be the reason I was getting error message since the following expression does not include null and incomplete date of birth?
[Derived Column [1014]] Error: An error occurred while attempting to perform a type cast.
[Derived Column [1014]] Error: The "component "Derived Column" (1014)" failed because error code 0xC0049064 occurred, and the error row disposition on "output column "DOB" (2292)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "Derived Column" (1014) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029.
Hi, I'm making a webapplication in C# with MSSQL as database. I've created a form to do an advanced search on books. The user can type in name, author, .... and he can also mark 2 dates from to Calendar objects (I made sure date one can not be bigger than date 2). I'm using smalldatetime as DBtype. The 2 selected values are DateTime in asp. The results are shown in a gridview. Since I added the feature I keep getting the same error and I can't find where it is. Here's some code: 1 public List GetBooks2(string invB,string titelB, string auteursB, string taalB, string uitgeverijB, string jaarB, string keywordsB, string categorieB, string standplaatsB, string ISBN,DateTime datum1, DateTime datum2, string sortExpression) 2 { //this is my method for the advanced search 3 using (SqlConnection oConn = new SqlConnection(_connectionString)) 4 { 5 string strSql = "select * FROM Boek where 1 = 1"; 6 if (!String.IsNullOrEmpty(invB)) strSql += " and (inventaris_nr like @invB)"; 7 if (!String.IsNullOrEmpty(titelB)) strSql += " and (titel like @titelB)"; 8 if (!String.IsNullOrEmpty(auteursB)) strSql += " and (auteurs like @auteursB)"; 9 if (!String.IsNullOrEmpty(uitgeverijB)) strSql += " and (uitgeverij like @uitgeverijB)"; 10 if (!String.IsNullOrEmpty(ISBN)) strSql += " and (ISBN10 like @ISBN or ISBN13 like @ISBN)"; 11 if (!String.IsNullOrEmpty(standplaatsB)) strSql += " and (standplaats like @standplaatsB)"; 12 if (!String.IsNullOrEmpty(jaarB)) strSql += " and (jaartal like @jaarB)"; 13 if (!String.IsNullOrEmpty(keywordsB)) strSql += " and (keywords like @keywordsB)"; 14 if (!String.IsNullOrEmpty(taalB)) 15 if (taalB == "Andere") 16 strSql += " and (taal NOT IN ('nederlands', 'frans', 'engels', 'spaans', 'italiaans', 'duits'))"; 17 if (taalB == "--Geen voorkeur--") 18 strSql += ""; 19 else 20 strSql += " and (taal like @taalB)"; 21 22 if (!String.IsNullOrEmpty(categorieB)) 23 if (categorieB == "--Selecteer een categorie--") 24 strSql += ""; 25 else 26 strSql += " and (categorie like @categorieB)"; 27 28 if (datum1 == null) 29 strSql += ""; 30 else 31 if (datum2 != null) 32 { 33 34 strSql+=" and datumB between @datum1 and @datum2"; 35 } 36 else strSql+=""; 37 if (!String.IsNullOrEmpty(sortBLOCKED EXPRESSION 38 strSql += " order by " + sortExpression; 39 else 40 strSql += " order by id"; 41 42 SqlCommand oCmd = new SqlCommand(strSql, oConn); 43 oCmd.Parameters.Add(new SqlParameter("@invB", "%" + invB + "%")); 44 oCmd.Parameters.Add(new SqlParameter("@titelB", "%" + titelB + "%")); 45 oCmd.Parameters.Add(new SqlParameter("@auteursB", "%" + auteursB + "%")); 46 oCmd.Parameters.Add(new SqlParameter("@taalB", "%" + taalB + "%")); 47 oCmd.Parameters.Add(new SqlParameter("@uitgeverijB", "%" + uitgeverijB + "%")); 48 oCmd.Parameters.Add(new SqlParameter("@jaarB", "%" + jaarB + "%")); 49 oCmd.Parameters.Add(new SqlParameter("@keywordsB", "%" + keywordsB + "%")); 50 oCmd.Parameters.Add(new SqlParameter("@categorieB", categorieB )); 51 oCmd.Parameters.Add(new SqlParameter("@standplaatsB", "%" + standplaatsB + "%")); 52 oCmd.Parameters.Add(new SqlParameter("@ISBN", "%" + ISBN + "%")); 53 oCmd.Parameters.Add(new SqlParameter("@datum1", "%" + datum1 + "%")); 54 oCmd.Parameters.Add(new SqlParameter("@datum2", "%" + datum2 + "%")); 55 56 57 oConn.Open(); 58 SqlDataReader oReader = oCmd.ExecuteReader(); 59 List boeken = GetBoekCollectionFromReader(oReader); 60 oReader.Close(); 61 return boeken; 62 } 63 }
I think that that method is correct, not sure though... The code for GetBoekCollectionFromReader(oReader) is this=1 protected List GetBoekCollectionFromReader(IDataReader oReader) 2 { 3 List boeken= new List(); 4 while (oReader.Read()) //THIS IS WHERE THE ERROR APPEARS 5 { 6 boeken.Add(GetBoekFromReader(oReader)); 7 8 } 9 return boeken; 10 }
That's the method where the error appears... Where should I place a breakpoint to get the exact location? To make sure all methods in this code are explained, here's the code for GetBoekFromReader(oReader))= 1 protected Boek GetBoekFromReader(IDataRecord oReader) 2 { 3 Boek boek = new Boek(); 4 boek.idB= (int)oReader["id"];//id auto generated dus verplicht 5 if(oReader["inventaris_nr"] != DBNull.Value) 6 boek.Inventaris_nrB = (string)oReader["inventaris_nr"]; 7 if (oReader["auteurs"] != DBNull.Value) 8 boek.AuteursB = (string)oReader["auteurs"]; 9 boek.TitelB = (string)oReader["titel"];//titel verplicht 10 if (oReader["taal"] != DBNull.Value) 11 boek.TaalB = (string)oReader["taal"]; 12 if (oReader["uitgeverij"] != DBNull.Value) 13 boek.UitgeverijB = (string)oReader["uitgeverij"]; 14 if (oReader["aantal_p"] != DBNull.Value) 15 boek.Aantal_pB = (string)oReader["aantal_p"]; 16 if (oReader["jaartal"] != DBNull.Value) 17 boek.JaartalB = (int)oReader["jaartal"]; 18 if (oReader["keywords"] != DBNull.Value) 19 boek.KeywordsB = (string)oReader["keywords"]; 20 if (oReader["categorie"] != DBNull.Value) 21 boek.CategorieB = (string)oReader["categorie"]; 22 if (oReader["standplaats"] != DBNull.Value) 23 boek.StandplaatsB = (string)oReader["standplaats"]; 24 if (oReader["ISBN13"] != DBNull.Value) 25 boek.ISBN13 = (string)oReader["ISBN13"]; 26 if (oReader["ISBN10"] != DBNull.Value) 27 boek.ISBN10 = (string)oReader["ISBN10"]; 28 if (oReader["URL"] != DBNull.Value) 29 boek.UrlB = (string)oReader["URL"]; 30 if (oReader["username"] != DBNull.Value) 31 boek.UsernameB = (string)oReader["username"]; 32 if (oReader["passwoord"] != DBNull.Value) 33 boek.PasswoordB = (string)oReader["passwoord"]; 34 if (oReader["datumB"] != DBNull.Value) 35 boek.DatumBoek = (DateTime)oReader["datumB"]; 36 if (oReader["status"] != DBNull.Value) 37 boek.StatusB = (string)oReader["status"]; 38 39 40 return boek; 41 }
Conversion failed when converting datetime from character string. That's the error I get by the way. It also appears when I do a search and I don't use the date function... for example when I only fill in the title textbox and I don't select any dates from the calendars... Sorry for the long post, but I think it's the only way to get a clear view on it...
I have a web page which executes a stored procedure with several parameters. On execution I get an error for the following piece of code. I will be passing null value to the stroed procedure for this parameter "ActiveDate". Dim parameterActivedate As SqlParameter = objCommand.Parameters.Add("@Activedate", SqlDbType.DateTime) parameterActivedate.Value = "" The error is: System.InvalidCastException: Conversion from string "" to type 'Date' is not valid. at Microsoft.VisualBasic.CompilerServices.Conversions.ToDate(String Value) at webpage.Do_Update(Object Sender, EventArgs e)
I am having an issue with a Report in SSRS against a Sybase db.
The dataset for the report is a Sybase stored proc which is passed a parameter (varchar). When I run the proc in Sybase it runs fine, when I run it in SSRS in the data tab it runs fine, but when I try to preview the report, or run from IE after deploying it I get the following error:
An error has occurred during report processing. (rsProcessingAborted) Get Online Help Query execution failed for data set 'Table_1'. (rsErrorExecutingCommand) Get Online Help The given type name was unrecognized
Looks like it is an error converting the string in SSRS to a varchar in Sybase, but I don't know how to fix it.
If I create a table with an identity (int) column and the varchar column, join this to the proc and pass the int as the parameter the report works fine!! (This is not an option in production though.)
I am using SybaseASE OLE DB Provider Version 02.70.0032
Conversion failed when converting datetime from character string. I am getting this error while inserting values in the DB from my application. I have done insertion before too , but i never receive this strange error but when I insert the same thing manually then i didn't get this error ...
The format I used is also yyyy-mm-dd then how to solve this error?????????
Trying to get this query to work, converting a binary version string to human readable output but somehow it doesn't work?
/* Version number binary from daily registy */ DECLARE @VERSION_STRING VARBINARY(16) = 0x4D5544532556564C5B504C552D675B; /* Inline Tally for parsing the binary string */ ;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)) ,NUMS(N) AS (SELECT TOP(DATALENGTH(@VERSION_STRING)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2)
I'm very new to SSIS so please be patient and very detailed. Thank you in advance!
I have a SQL Table (tblImporData) with 2 columns: "Data" (varchar(40)) and "AccountNumber" (varchar(7)). I need to take the information in the "Data" column and parse it out into 3 different fields into a new table. I created the new table (tblExportData) with the following fields and data types: AcctNumber (varchar(7)), SiteName(nvarchar(10)), Balance (numeric(8,2)), and Active (nvarchar(1)).
Example of Data Field that I'm breaking up: 01Binford 001999Y
I've created a SSIS Package and in the dataflow I have an OLE DB Source going into a Derived Column. The derived column has the following information to split the data.
Derived Name Derived Column Expression Data Type Ln AcctNumber Replace 'AcctNumber' AcctNumber String[DT_STR] 7 SiteName <add as new column> SUBSTRING(Data,3,10) Unicode string 10 Balance <add as new column> SUBSTRING(Data,13,6) Unicode String 6 Active <add as new column> SUBSTRING(Data,19,1) Unicode String 1
So it should split my example into the following: AcctNumber SiteName Balance Active 12345 Binford 001999 Y
Everything works fine if the table I'm dumping the information to in SQL has varchar data types for all columns. But I need to convert my balance field from 001999 to 19.99. I tried putting a data conversion transform in that has the input column = Balance, Output Alias = Balance, Data Type = numeric [DT_NUMERIC], Precision = 8, and Scale = 2. I then changed the "Balance" data type to numeric(8,2) for the SQL table I'm dumping to, and added an OLE DB Destination to that table and mapped the columns.
When I try to run the package I'm getting an error at the Data Conversion that says the following:
[Data Conversion [698]] Error: Data conversion failed while converting column "Balance" (162) to column "Balance" (724). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.". [Data Conversion [698]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Balance" (724)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Balance" (724)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (698) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. [DTS.Pipeline] Error: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
So, what am I doing wrong? Is there a different way I should be doing this? As always, your help is appreciated more than you'll ever know!
I use the derived column to convert a string date from a flat file like this: "Jan 02 2005" into a datetime. I have seen in the forum to use: (DT_DATE)(SUBSTRING(mydate,5,2) + "-" + SUBSTRING(mydate,1,3) + "-" + SUBSTRING(mydate,8,4)) However, even if it produces a string like '02-Jan-2005', the following cast to dt_date fails. I have also tried inverting month and day, year/month/day but all with the same result:
Derived Column [73]] Error: The component "Derived Column" failed because error code 0xC0049064 occurred, and the error row disposition on "output column"...
I think the cast fails bacause of the month format. Therefore the only solution would be to code in in a lookup table Jan, 01 | Feb, 02 |... ????
This is probably a simple wuestion but i would appreciate some help I am trying to get and insert the userId into a table called "Orders" I got the user id as Dim CustomerId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString The button on click command is worldshop.ShoppingCart.CreateOrder(CustomerId,......................... The Function Has the parameter for Customer Id as follows Dim dbParam_CustomerId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_CustomerId.ParameterName = "@CustomerId" dbParam_CustomerId.Value = CustomerId dbParam_CustomerId.DbType = System.Data.DbType.String command.Parameters.Add(dbParam_CustomerId) When i run this I am getting the error message "Conversion failed when converting from a character string to uniqueidentifier." Can anyone tell me where I am going wrong many thanks Martin
Hi, i have a problem, i keep getting this Error. I want to insert an uniqueidentifier using a textbox, i use the following code to insert. SqlDataSource1.InsertParameters["RWID"] = new Parameter("RWID", TypeCode.String, RWID); SqlDataSource1.Insert(); The databasetype is an uniqueidentifier of that column. Anyone who can help me with this problem?
I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.
Conversion failed when converting from a character string to uniqueidentifier. My Code is shown below: comSQL.Parameters.AddWithValue("@PROPERTYID", Format(Request.QueryString("ID").ToString, "{0:########-####-####-####-############}")) This has worked before but isnt' anymore. Any ideas?
I am trying to write a SP in Query Analyzer before I start the code from a web form to acces it. Here is the SQL: ------------------------------------------- DECLARE @cReportGroups Varchar(500)DECLARE @StartDateRange DateTime DECLARE @EndDateRange DateTime SET @cReportGroups = '''VCCEddieBauer'',''VCCEddieBauerDirect'', ''VCCEddieBauerOrder'', ''VCCEddieBauerOvernight'', ''VCCEddieBauerSpanish'', ''VCCEddieBauerCustomerService''' SET @StartDateRange = '09-16-2007' SET @EndDateRange = '01-31-2008' BEGIN Declare @sql As Varchar(800) Set @sql = 'SELECT ttalkacd, cName, dIntervalStart, cReportGroup' + ' FROM IAgentQueueStats ' +' WHERE dIntervalStart >= ' + @StartDateRange ---- ' AND dIntervalStart <= ' + EndDateRange + ---- ' AND cReportGroup IN (' + @cReportGroups + ')' + ---- ' AND cHKey3 = ''*''' + -- ' ORDER BY dIntervalStart' --+ ---- ' COMPUTE SUM(ttalkacd)' --Print(@sql) exec (@sql) END ----------------------------------------------- Just trying to get the first >= filter working before I fill in the >= working to complete the date range logic. But I keep getting this conversion error. Why does SQL think that anything is a character string?All dates are declared as DateTime.I have tried a bunch of combinations like Convert(DateTime, @StartDateRange, 101).Nothing seems to work.
i'm using a "data conversion" object to convert a numeric field to a string just before i save the record set to the database.
the problem is when this numeric field is > 0 it looses the precision on its decimal value.
example, if numeric value is 0.32
after converting this to a string, the new value will be : .32
it's lost the 0 infront of it. i can't do this converion in the query level because it's a derived field, so i need to convert it to a string before stroing it.
when converting to string i'm using the code page 1252 (ANSI - Latin I). i also tried with unicode string both looses this 0 infront.
I received the above error message when I try to convert a field to a date.
Here is the line of sql:
convert(datetime, cast(EligibilityEndDate as char(8)), 112) as EligibilityEndDate,
EligibilityEndDate is stored as an integer in our source system, so dates display 20080201.
Looks like the data is not a correct date. If I just bring in the field as is, and load results into a text file or Access, I can't see the offending record. The query returns around 320K records.
Is there another way of finding the invalid record(s)?
Hi, I am trying to insert an entry into my database, but I keep on getting the following error: "Conversion failed when converting datetime from character string." My query is a parameterized T-SQL query (i.e. "INSERT INTO blah (name, dob) VALUES (@name, '@dob')"), and I have initialized the SqlCommand object by doing: sqlCmd.Parameters.AddWithValue("@name", "Captain America"); sqlCmd.Parameters.AddWithValue("@dob", birthdate.ToString("MM/dd/yyyy h:mm tt");
sqlCon.Open(); sqlCmd.ExecuteNonQuery(); sqlCon.Close(); The dob field in the table 'blah' is a smalldatetime datatype, and in the code above, the birthdate object is a DateTime object. I can insert into my database using a normal T-SQL through Management Studio, For example, INSERT INTO blah (name, dob) VALUES ('Captain America', '11/27/1923 12:34 PM') But it doesn't work when I try doing it the way I have listed above. It's really annoying because I don't know how to debug or if it is possible to debug the query. So first off, I want to ask, does anyone know how why this exception is occuring? Secondly, is it possible to debug the query? and if yes, how? And finally, is there a way for me to extract the T-SQL query sent to the SQL Server with the parameters filled in their values? For example, after adding all parameters to the SqlCommand via sqlCmd.Parameters.AddWithValue(), can I get the T-SQL query that is sent to the server?
Hi,I'm trying to concatenate a Description (nchar(100)) and Date(datetime) as Description and my initial effort was just"...description+' '+open_date as description..." which throws a date/string conversion error; finally came up with a working string belowbut don't think it's the optimal way to do this - any suggestions?select (rtrim(description)+''+rtrim(convert(char(2),datepart(mm,open_date)))+'/'+convert(char(2),datepart(dd,open_date))+'/'+convert(char(4),datepart(yyyy,open_date))) as description fromoncd_opportunity where opportunity_id=?open_date is not a required field at the db level, but it is requiredon the form so it should not be null as a rule.
