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.
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
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 othersThanks
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 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'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:
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:
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
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."
I have a table with the following structure: Table: Meeting ID integer not null Desc text null
Here Col 'Desc' is a text type stores description. I have used the following UPDATE query: UPDATE Meeting SET Desc='New Updated text......' + ' ' + CAST(Desc as varchar) WHERE ID=100
Issues: 1. Since i did'nt specify lenth for varchar it takes only 30 chars from existing value and added to the edited text. 2. If i give like this CAST(Desc as varchar(8000)); it might take only first 8000 chars; remaining would be truncated correct? I assume that will happen. 3. If i don't use CAST i get error msg: "Invalid operator for type.." Is there any work around in this situation? or better way to do it.
Hi, I'm very new to SQL. Trying to sum two fields, but I have to change the datatype first. Here's the code and the error message I receive. Any help would be appreciated.
SELECT TS_RESPONSIBLE, TS_STATUS, TS_USER_07, SUM(TS_USER_07 * TS_STATUS) AS value FROM TEST_54_VW GROUP BY TS_RESPONSIBLE, TS_STATUS, TS_USER_07 WHERE CAST(TS_USER_07 AS INTEGER)
I am using MS-Access as a front end for my MS-SQL DB. I have a sql view thatuses the following:SELECT TOP 100 PERCENT RECID, PATNUMBER AS [PAT #], SVCCODE AS [ServiceCode], QTY, PROF_CHRGS AS [Pro Fee's'], AMOUNT,BILLDATE AS [Bill Date], CHKAMT AS [Check Amt], PSDATEAS [Service Date], POSTDATE AS [Post Date], TRNSCODE AS [T Code],TRLR AS [T Code Desc], SUBSTRING(CAST(SVCCODE ASvarchar), 1, 4) AS [Dept #]FROM dbo.PAT_TransactionsORDER BY PATNUMBER, SVCCODEMy problem is the cast command. Will this sql view works and cast theSVCCODE field into a varchar, I need to cast the reseult of this,SUBSTRING(CAST(SVCCODE AS varchar), 1, 4) AS [Dept #], back in to a decimalformat. I am dropping this view onto a form and need to link to a field onthe form that is in decimal format.The only way I could get this to work was to create another view, based onthe one above, and cast the [Dept #] field back into the decimal format. Isthere any better way to do this? Can I nest the Cast command?Hope this makes sense.Thanks in advance.Mikem charney at dunlap hospital dot org
i have looked at several guides, and have come up with some code, although for some reason i am unable to get it to work, and receive an error:
Msg 6260, Level 16, State 1, Line 1 An error occurred while getting new row from user defined Table Valued Function : System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Object[]'. System.InvalidCastException: at UserDefinedFunctions.FillRow(Object obj, String& PID) .
Below is my code: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
using System.Collections;
public partial class UserDefinedFunctions { const string SELECT = @"SELECT pointData.PID FROM pointData INNER JOIN pointDevices ON pointData.PID = pointDevices.PID WHERE pointDevices.UUID = @UUID AND DATEADDED >= @DATE_START AND DATEADDED <= @DATE_STOP ORDER BY DATEADDED";
I'm trying to cast the variable Alumni and keep getting a "The type of the value being assigned to variable "User:: Alumni" differs from the current variable type" error. The resultset from Records is a varchar(max) and the variable Alumni is a string. I found a blog that says to cast the result column to a max size limit. I want to do it this way so I can put the result in the message of a send mail task.
Here is the blog. http://blogs.conchango.com/stevewright/archive/2006/01/20/2686.aspx
SELECT Records as Alumni
this doesn't work or I dont' have the syntax right? SELECT Records as CAST(Alumni as varchar(max))
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=CannotAssignExpressionToProperty&LinkId=20476
We have a 3rd party application that connects to our SQL 2005 as a external database source.
Our external database has a 'datein' field that for some reason is stored as a varchar(50).
I can use cast to convert the field to datetime, but how would I then format to the yyyy-mm-dd format in the same select statement(using in view)?
I need to convert this field to a format of YYYY-MM-DD in order for the 3rd party application to search on it correctly. Below is an example of the data -- FieldName = DateIn DateType = Varchar(50) 2006-07-06 Sep 12 2007 11:16AM 2007-07-19 Oct 2 2007 12:24PM 2007-08-19 Oct 31 2007 8:00PM Sep 15 2007 9:25AM 2007-08-18 Oct 16 2007 7:01PM Oct 30 2007 11:15AM Aug 29 2007 8:54AM Aug 29 2007 8:38PM Aug 25 2007 2:33PM
I'm encountering a Cast error, and find I am very much out of my league... I'm using a Derived Column Transformation to convert a column that comes from an Excel spreadsheet from a non-standard date format to DT_Date, though I picked this format simply because it was the first date format I found in the pick list. When I upload the ETL into Management Studio and run it I get the following error:
Code Block
Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted 01/03/2008 07:27:52,Force_Receipt_To_Clear_From_Excel,Error,0,WFM-SQL1-RDM,Force_Receipt_To_Clear_From_Excel,(Job outcome),,The job failed. The Job was invoked by User Removed. The last step to run was step 1 (Run Force_Receipt_To_Clear_From_Excel).,00:00:35,0,0,,,,0 01/03/2008 07:27:52,Force_Receipt_To_Clear_From_Excel,Error,1,Sever_Name_Removed,ETL_NAME_REMOVED,Run ETL_NAME_REMOVED,,Executed as user: Removed. ....00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 07:27:52 Error: 2008-01-03 07:28:25.12 Code: 0xC0202009 Source: Data Flow Task OLE DB Destination [232] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Invalid character value for cast specification". End Error Error: 2008-01-03 07:28:25.14 Code: 0xC020901C Source: Data Flow Task OLE DB Destination [232] Description: There was an error with input column "ASSIGNED_TO_DISPATCH" (2020) on input "OLE DB Destination Input" (245). The column status returned was: "Conversion failed because ... The package execution fa... The step failed.,00:00:35,0,0,,,,0
The data that is being converted appears as one of two things. First it can appear as a string value with 0 characters (that does not appear a Null oddly). Second it will appear as a string value representing a date in the following format 01022008 7:38 which is a date in the following the format 2 Digit Month, 2 Digit Day, 4 Digit Year, Space Time in Military Time.
The expression I'm using to convert the String data type to a Date is:
Code Block
LEN([Completed Date Time]) == 0 || ISNULL([Completed Date Time]) ? NULL(DT_DATE) : (DT_DATE)(SUBSTRING([Completed Date Time],1,2) + "/" + SUBSTRING([Completed Date Time],3,2) + "/" + SUBSTRING([Completed Date Time],5,10))
I have a table that stores a value called "LocationNumber" as a varchar. I didn't create the database, I just use it. Anyway I usually sort the list by Casting the LocationNumber to an Integer. If I don't cast it, it trys to sort it lexiographically i.e. 0, 1, 21, 3, 345, 9 instead of 0, 1, 2, 3, 9, 21, 345.
It works 99.9% of the time, but the other 0.1% of the time it runs into some text that somehow made its way into the database field, and it causes an error.
Is there a way to safely cast a number in SQL? So that it just returns 0 when the value is not a number.
