I am pretty new to SQL for SQL Server 2005.
In a view I have a column CAST(Field1 as int). Field1 is a varchar.
All works well except when the data is not numeric such as a '?' or '*'.
How can I get around this, with a case statement, coalese?
I only want to perform the CAST on valid numeric values.
The valid values in the varchar field are blank, null,'0','1','2','3','4','5','?','*' and maybe others
Thanks
I have developed a CF application and now need to add some charts to it for admin purposes. I have a form page that uses a calendar widget to let a manager select a date range and submits to display data for that timeframe. I need to convert the formfields to a date for my WHERE clause in my query. My experience to this point has just been simple queries of one table. below is a sample I found and modified numerous time to try and make it work with no success.
Can anyone give me a hand with this?
SELECT COUNT ((CHG_TYP_CD)) AS CHG_TYP,
CHG_TYP_CD
FROM WRK_RQS
WHERE(CAST('form.startdate' AS datetime) >= RQS_SBM_TS)
AND
(CAST('form.startdate' AS datetime) <= RQS_SBM_TS)
GROUP BYCHG_TYP_CD
Hi,
Can I convert from float to varchar without trunc the values? Can I use any mask like '#.##'?
from -> cast ( 123.44 as varchar(256) )
result = '123.44'
thanks,
Below is my query.Its working great if i remove ,Cast(C.ClassTime as time) as StartDate.But when i use this i get an error as The conversion of a varchar data type to a datetime data type resulted in an out-of-range value My ClassName is a varchar.Whose definition i cant change to DateTime now.But i want to cast it to DateTime.
Code:
Select C.ClassID as Appointment_Id,C.ClassName as Appoitment_Descr,Cast(C.ClassTime as time) as StartDate,C.EndClassTime as EndDate, 'Class' as Type
From Dojo D inner join DojoClass C on D.SchoolID = C.DojoSchoolID
Where D.SchoolID = @DojoID
and C.Days like'%' + @Days + '%'
Union
Select E.DojoEventID as Appointment_Id,E.EventName as Appoitment_Descr , E.EventStartDate as StartDate , E.EventEndDate as EndDate,'Event' as Type
From Dojo D inner join DojoEvent E on E.DojoID = D.SchoolID
Where D.SchoolID = @DojoID and @Date
Between E.EventStartDate and E.EventEndDate
how can i cast it correctly?
1)Cast(isnull(Partl,0) as smallint)
2) convert(varchar(12),newDate, 101)
what two lines would do. ??
thanks,
I try to run code but got an error. How to fix it?
SELECT Cast(('20140304800084500001') AS BIGINT)
Arithmetic overflow error converting expression to data type bigint
I have a column (varchar (50)), It has values from 1-12.
I want to cast them to their appropriate months.
Example: 1 = January
2 = Febuary
3 = March
etc.
I have a varchar(len=9) field that I want to cast as numeric(9,2). Some of the field values are null but some have valid values (ie 1.00 or .05).
I am selecting data from one table and then updating a different table.
I have a select statement within an insert statement and then an update statement. I've tried a cast statement but not successfully. I get the following error.
[Execute SQL Task] Error: Executing the query "Exec sp_ESTLoadPOData" failed with the following error: "Error converting data type varchar to numeric.". Possible failure reasons:
Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
how do you get it to sort correctly for the date? at the minute its sorting on the characters rather than actual date value
Code Snippet
INSERT INTO @TempItems (OrderID)
SELECT OrderID
FROM Orders o
INNER JOIN Customers c ON c.CustomerID = o.CustomerID
INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID
ORDER BY
CASE @SortOrder --Order ASC
WHEN 0 THEN cast(OrderID as varchar(100))
WHEN 1 THEN cast(c.CompanyName as varchar(100))
WHEN 2 THEN cast(e.FirstName as varchar(100))
WHEN 3 THEN cast(o.OrderDate as varchar(100))
WHEN 4 THEN cast(o.RequiredDate as varchar(100))
WHEN 5 THEN cast(o.ShippedDate as varchar(100))
ELSE '1'
END,
Hi I have a varchar(8000) and currently XML files are stored in varchar(8000).Some times when i am doing manuplactions in my varchar column i am getting with special characters error. so now i want to keep my column varchar(MAX) and when i am doing calculations i will convert my varchar datatype to xml datatype. By doing this i hope there wont be any special character problems.
When i am doing calculations with the wellformed xml i am getting error for both convert and cast methods as below
I am trying to do convert(xml,MyVarcharColumn)
Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.
Also i tried with casting and getting same problme. is there any way to convert
please suggest me
Thanks
Dilip
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
Any help is appreciated.
For the life of me I cannot figure out why SSIS will not convert varchar data. instead of using the table to table method, I wrote a SQL query so that I could transform the datatype ntext to varchar 512 understanding that natively MS is going towards all Unicode applications.
The source fields from Access are int, int, int and varchar(512). The same is true of the destination within SQL Server 2005. the field 'Answer' is the varchar field in question....
I get the following error
Validating (Error)
Messages
Error 0xc02020f6: Data Flow Task: Column "Answer" cannot convert between unicode and non-unicode string data types.
(SQL Server Import and Export Wizard)
Error 0xc004706b: Data Flow Task: "component "Destination - Query" (28)" failed validation and returned validation status "VS_ISBROKEN".
(SQL Server Import and Export Wizard)
Error 0xc004700c: Data Flow Task: One or more component failed validation.
(SQL Server Import and Export Wizard)
Error 0xc0024107: Data Flow Task: There were errors during task validation.
(SQL Server Import and Export Wizard)
DTS used to be a very strong tool but a simple import such as this is causing me extreme grief and wondering of SQL2005 is ready for primetime. FYI SP1 is installed. I am running this from a workstation and not on the server if that makes a difference...
Any help would be appreciated.
I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars). Right now its set up for varchar(max) and I don't think I want to do this.
How does varchar(max) store info differently from varchar(250)? Either way doesn't it have to hold the container information? So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?
Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?
Should I just go with char(250) and watch my db size explode?
Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.
Any insight to this would be appreciated.
Hey,
I was doing some research on how SQL stores data on disk.
MSDN states that when storring a varchar, only the length of the data itself is used plus two bytes.
So, if you store "car" in a VarChar(50) it will take 5 bytes.
But when you store "car" in a VarChar(500) it will also take 5 bytes.
What is the reason users should define the parameter lenght?
Can you use VarChar(8000) whole the time, without any drawback?
Thanks
Hi
Can anyone tell me how I can CAST these fields AS DECIMAL (19, 2) please?
(SELECT ([Total students] - [withdrawn] - [transferred] - [cancelled]) / [total Students] * 100 AS [Percentage Retained])
Thanks
Daniel
I have
CASE
WHEN (a.type_id in (9))
THEN a.duration
ELSE 0 --CAST ( expression AS data_type )
END 'Time'
I need the duration to be displayed if type is 9 only.
so otherwise id ideally want 'N/A' displayed..
but it won't display it as its not an int ..
is there a way i can just display it temporarily
instaed of 0 ?
I am having a little trouble with the CAST in my SELECT statement below. Any help is greatly appreciated.
SELECT
group_id_ AS [Group ID],
vendor_id_ AS [Vendor ID] ,
project_id_ [Project ID],
resource_id_ [Resource ID],
vendor_price_ [Old Price],
new_price_ [New Price],
(CAST (new_price_/vendor_price_)-1 AS DECIMAL (4, 2)) AS [Difference]
FROM hbs_vnpq
WHERE (group_id_ = '210') AND (vendor_id_ = '08416') AND (new_price_ >0) AND (vendor_price_ >0)
ORDER BY [Difference]
Server: Msg 1035, Level 15, State 10, Line 7
Incorrect syntax near 'CAST', expected 'AS'.
im doing a sum on a table and it either returns a number in decimal format or 'null' . The problem is when it returns null i want it to just make the text say '0.00'. So i did a test on the object that if it returns NULL just print '0.00' but if it is not null it tells me that there is a number there and i want to store that as a decimal and print it out. But i get an error for a type cast when im not it should not even be going to that part of the code. In the code below the first executescaler will return null so it should just go straight to the else. But it gives me the type cast error in the if that shouldnt be seen. The error and code are below. //Borrower NSF FEES
cmd.CommandText = "select sum(itemamount) from postmtdtls where loanid='" + LoanID + "' and Transactioncode = '310'";
object temp = cmd.ExecuteScalar();
if (temp != null)
{
decimal B_NSFFees = ((decimal)cmd.ExecuteScalar());
borrowerPayoff_NSFFees.Text = String.Format("{0:#,#.##}", B_NSFFees).ToString(); //Borrower NSF FEES
}
else
{
borrowerPayoff_NSFFees.Text = "0.00"; //borrowerPayoff_NSFFees.Text = "0.00";
} Server Error in '/WebSite5' Application. Specified cast is not valid. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Specified cast is not valid.Source Error: Line 774: if (temp != null)
Line 775: {
Line 776: decimal B_NSFFees = ((decimal)cmd.ExecuteScalar());
Line 777: borrowerPayoff_NSFFees.Text = String.Format("{0:#,#.##}", B_NSFFees).ToString(); //Borrower NSF FEES
Line 778: }Source File: c:ProgrammingFilesWebSite5InvestorPool.aspx.cs Line: 776 Stack Trace: [InvalidCastException: Specified cast is not valid.]
InvestorPool.GetLoanInfo(String LoanID) in c:ProgrammingFilesWebSite5InvestorPool.aspx.cs:776
InvestorPool.MortAccountText(Object sender, EventArgs e) in c:ProgrammingFilesWebSite5InvestorPool.aspx.cs:660
System.Web.UI.WebControls.TextBox.OnTextChanged(EventArgs e) +75
System.Web.UI.WebControls.TextBox.RaisePostDataChangedEvent() +124
System.Web.UI.WebControls.TextBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
System.Web.UI.Page.RaiseChangedEvents() +138
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4507
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
If I run the following query in SQL Server Management Studio it returns the correct results: (Searching the table for the field "SpecimenID (an INT)" against the data entered (a Text Field - "7575-01") from the submitted form.
SELECT ClinicalID, SpecimenID, PatientID, LabID, Accession, Bacillus, Francisella, Yersinia, Brucella, Burkholderia, Coxiella, Staphylococcus, Other, OtherExplanation, CollectionDate, strddlTransportMedium, strddlSpecimenSource, UserName, Test, SpecimenCount, DateAndTimeFROM ClinicalSpecimenWHERE (SpecimenID = CAST('7575-01' AS VARCHAR(50)))ORDER BY SpecimenID DESCHowever, when I try to use the same logic in the ASPX.VB code behind page, as follows below, I either get an error message (Syntax error converting the varchar value '' to a column of data type int.) or record not found.... Can someone please explain what I am missing here....
MySQL = "SELECT * FROM ClinicalSpecimen WHERE SpecimenID = CAST(('" & AccessionPresent & "') AS VARCHAR(50))"
*"AccessionPresent" is the value of the text field retrieved from the form.
I guess what I am really asking is how can I search for an INT value in a table using a VARCHAR Field.
Thank you for any or all assistance !!!
Hi,I want to turn int to double/decimal in microsoft sqlSHould i use cast or convert?if so, how i do it thanks,
View 1 Replies View RelatedHi over there,I hope this question is not too simple, but I didn't manage to figure out why...I would need an explanation for following issue:I'm reading data from a database (MSSQL) and it the column "PersonBirthday" is DBNull.I wanted to prevent the error (Textbox.Text = DBNull) with an IIF. The thing is I get thistypecast exception:"Conversion from type 'DBNull' to type 'Date' is not valid." This code is NOT working, why? txtPersonBirthday.Text = IIf(IsDBNull(.Item("PersonBirthday")) =
True, String.Empty,
CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd")) When I'm using this code, which is for me obviously the same, just with an if-block it works,and I want to know why - please explain. If IsDBNull(.Item("PersonBirthday")) Then txtPersonBirthday.Text = String.Empty Else txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd") End IfThanks in advance,cheers,uquandux If IsDBNull(.Item("PersonBirthday")) Then txtPersonBirthday.Text = String.Empty Else txtPersonBirthday.Text = CDate(.Item("PersonBirthday")).ToString("yyyy-MM-dd") End If
Can anyone tell me what varbinary casts to ?? can I do this int[] temp=(int)DataReader["varbinaryColumn"]. Or is byte[] temp the appropiate protocol.
View 1 Replies View RelatedCan't seem to find why I'm getting this error: Specified cast is not valid.
Ok, using a stored procedure for SQL Server 2000 and here is the main part of it:
SELECT id, rank, firstName, lastName, service, status, createdTime FROM accessRequest WHERE lastName LIKE @tLastName AND firstName LIKE @tFirstName
And the C# code behind from the class file:
SqlDataReader spResults;
conn.Open();
spResults = command.ExecuteReader();
while( spResults.Read() )
{
AccessRequestSearch request = new AccessRequestSearch( (int)spResults.GetInt32( 0 ), spResults.GetString( 2 ), spResults.GetString( 3 ), spResults.GetString( 1 ), spResults.GetString( 4 ), spResults.GetString( 5 ), Convert.ToDateTime(spResults.GetString( 6 )));
searchResults.Add( request ); // Add to Array List
}
spResults.Close();
The part in red is where I think it's happening because that is what I just added to the request. createdTime in the table is set as DateTime.
Can anyone see what I am missing here?
More info is available if needed.
Thanks,
Zath
I have a table with a column called Sample_Date_and_Teime with a row definition of char(16). What is stored in this table is a date (ie. 1020621141517000) The first char is the century, the next are the year, the next two are the month, the next two are the day, the next 2 the hour, the next two are the minutes, and the next 2 are the seconds and the last 3 are to be ignored... what I need to do is write a select statement that converts this column into a datetime so that I can then do a insert into a new table by selecting from this table based on a date range. PLEASE HELP:confused: :confused: :confused:
View 1 Replies View RelatedI need Query syntax to cast/convert values as follws.
Val.: 00005000010260002180 - Result must be: 5.1.2600.2180
Val.: 00005000000213400001 - Reslut must be : 5.0.2134.1
Dots must also be contained in result
I use to import data from DBF Clipper databases into SQL Server. When a table is just imported its date fields have string format. I need to copy their data to tables of database where they ahve to be converted into date. Direct operator INSERT doesn't convert properly (I've not successed in changing default date format so it'll be covertable) but using CAST I can get result of strings like 13.05.1970 0:00:00 as datetime type. But not all records can be coverted this way. For ones can't be converted I've solved to make NULL fields there. But I don't know how to make CAST operation return NULL when convertion isn't possible. The query
INSERT INTO people_temp
(reg_num, surname, stname, patronymic, foreing, gender, birthdate, fam_pos, dwell_type, children, nation, par_not, region, stud_fml, parn_fml,
com_prob, sp_prob, sn_passport, nn_passport, dv_passport, wg_passport)
SELECT STUDENTs_temp.REG_NOM, STUDENTs_temp.FAMILY, STUDENTs_temp.NAME, STUDENTs_temp.PARN_NAME, STUDENTs_temp.INOSTR,
STUDENTs_temp.SEX,
CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,
PSPR_temp.NATION,
PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,
PSPR_temp.PARN_FML,
PSPR_temp.OB_STAJ, PSPR_temp.SP_STAJ, PSPR_temp.SN_PASPORT, PSPR_temp.NN_PASPORT, PSPR_temp.DV_PASPORT,
PSPR_temp.WG_PASPORT
FROM STUDENTs_temp INNER JOIN
PSPR_temp ON STUDENTs_temp.REG_NOM = PSPR_temp.REG_NOM
gets an error 'The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value'. Tell me please how can make type casting return NULL if convertion isn't possible.
It must be something I'm overlooking but I keep getting an error message that this statement can't parse.
UPDATE product SET supplier = LEFT(supplier,LEN(supplier-4)) + CAST( '2100' AS varchar(4)) WHERE actualid = 'IS2100-CO2-CO2-0-4-I'
Any help would be greatly appreciated.
Have the following in my SELECT statement, which I'm having issues with when I modify it to include a CAST or a CONVERT.
CONCAT(PER.[PERSON-REF],ROW_NUMBER() OVER (PARTITION BY PER.[PERSON-REF] ORDER BY TEN.[tenancy-ref])) AS 'ID'How do I convert or cast it to a varchar (20)?
I found this nifty code on stackoverflow that works well but I'm trying to send the results to a text file and the column lengths are huge. I used CAST for the first line and it worked great but I can't seem to make it work with duration. Here's the original code:
SELECT
j.name,
h.run_status,
durationHHMMSS = STUFF(STUFF(REPLACE(STR(h.run_duration,7,0),
' ','0'),4,0,':'),7,0,':'),
[code]....
how to run a CAST on DURATION?
I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.
So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?
CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings
Hi all,
i need help for deleting the time from a datetime type
what i mean is that instead of
2007-08-15 01:30:00.000
i would get
2007-08-15 00:00:00.000
many thanks
Hi ,
I have a problem with casting a string number to int.
for exmaple i have the number '0110' (string) and i would like it to be 110. or '0001' to be 1.
i tried to do:
cast(MyNUM as int) .
but i get "Syntax error converting the nvarchar value 'O322' to a column of data type int."
is anyone have any idea how to solve it ?
thanks