Specified Cast Is Not Valid

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;


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



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.



View Replies


Specified Cast Is Not Valid

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
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

View Replies View Related

Error: Specified Cast Is Not Valid.

System.Data.SqlClient.SqlDataReader Dil1;
Dil1 = sqlDilGetir.ExecuteReader();
ddlDil1.Items.Add(new ListItem("1. Dil", ""));
while (Dil1.Read())
ddlDil1.Items.Add(new ListItem(Dil1.GetString(1), Dil1.GetString(0)));

View Replies View Related

SSRS2000 - Specifies Cast Not Valid



I get the above error everytime I try to export to Excel; exporting to PDF works fine.


The only way I can get rid of the error is by deleting all the data from within the report (not ideal!).  Anyone got any ideas on what could be causing this?


Many thanks,



View Replies View Related

Data Import Wizard, Specified Cast Is Not Valid

I want to import database from pervasive to SQL Server 2005. And in data Import wizard when i select the data source as .Net Framework Data provider for ODBC and give the required information e.g. dsn, driver etc. it gives the error :
Cannot get the supported data types from the database connection,

Additional Information
Specified cast is not valid. (System.Data)
Whats the problem ????

View Replies View Related

Cannot Show The Editor For This Task. Specified Cast Is Not Valid

This error is pretty persistent. I re-installed VS 2005 and SQL Server 2005 but did not help. Every time I try to open a Script Task editor it gives me the same error regardless of the project or package. DO I NEED TO REBUILD MY SYSTEM ?


Cannot show the editor for this task. (Microsoft Visual Studio)


Specified cast is not valid. (Microsoft.VisualBasic.Vsa.DT)

Program Location:

   at Microsoft.VisualBasic.Vsa.Dt.VsaIDE.get_ExtensibilityObject()
   at Microsoft.SqlServer.VSAHosting.DesignTime.get_ExtensibilityObject()
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskUI.Dispose()
   at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTaskMainWnd.Dispose(Boolean disposing)
   at System.ComponentModel.Component.Dispose()
   at Microsoft.DataTransformationServices.Design.DtrPackageDesigner.DoDefaultActionForTask(TaskHost task)



Any help is appreciated.




View Replies View Related

Cast From String 'OPEN' To Type 'Double' Is Not Valid.

Hi.. Please help me resolve this error "Cast from string 'OPEN' to type 'Double' is not valid.". Error here If CallStatus = 10 Then ....Code:Public Sub UpdateCallStatus()        Dim CALLID, RequestorID, CommentsFromITD, MessageFromITD, MessageToITD, CallStatus, strSQL As String        CALLID = Request.QueryString("CallID")        RequestorID = Session("USER_ID")        CommentsFromITD = lblcomments.Text        MessageFromITD = lblmessage.Text        MessageToITD = txt_desc.Text        CallStatus = Trim(Request.Form(ddl_callstatus.UniqueID))
        Dim ObjCmd As SqlCommand        Dim ObjDR As SqlDataReader
        Try            If CallStatus = 10 Then                strSQL = "UPDATE CALLS  SET STATUS_ID=" & CallStatus & " WHERE CALL_ID=  " & CALLID & ""                ObjCmd = New SqlCommand(strSQL, ObjConn)                ObjConn.Open()                ObjDR = ObjCmd.ExecuteScalar()                gbVariables.insertuserevents(CALLID, RequestorID, "Call Closed")                Response.Redirect("UserCallClosed.aspx")                ObjConn.Close()            Else                strSQL = "UPDATE CALLS  SET STATUS_ID=" & CallStatus & " WHERE CALL_ID=  " & CALLID & ""                ObjCmd = New SqlCommand(strSQL, ObjConn)                ObjConn.Open()                ObjDR = ObjCmd.ExecuteScalar()                ObjConn.Close()
                strSQL = "SELECT STATUS_LABEL  FROM STATUS WHERE STATUS_ID = " & CallStatus & ""                ObjCmd = New SqlCommand(strSQL, ObjConn)                ObjConn.Open()                ObjDR = ObjCmd.ExecuteScalar()                ObjConn.Close()
                gbVariables.insertuserevents(CALLID, RequestorID, CallStatus)                CallStatus = ""            End If        Catch ex As Exception            lblmsg.Text = ex.Message.ToString        End Try    End SubThanks...

View Replies View Related

Specified Cast Is Not Valid Error In Datagrid/update Script

I have a datagrid script where I modify data in an sql dbase in asp.net, when i hit the "update" button, I get a Specified cast is not valid error on my 'descript' declaration, whereas 'descript' is a multiline text box and a varchar datatype (everything else is either a char or a datetime datatype). Am I assigning 'descript' a wrong datatype? Tried making it a nvarchar, still get same result'''''''''''''''''''''''''''''''Sub MyDataGrid_UpdateCommand(s As Object, e As DataGridCommandEventArgs ) Dim conn As SQLConnection Dim MyCommand As SQLCommand Dim strConn as string = "Server=sql.mydomain.com;Initial Catalog=mydb;User ID=DBxxx;Password=xxxxx;" Dim company As textbox = E.Item.cells(2).Controls(0) Dim address As textbox = E.Item.cells(3).Controls(0) Dim city As textbox = E.Item.Cells(4).Controls(0)  Dim state As textbox = E.Item.cells(5).Controls(0) Dim county As textbox = E.Item.cells(6).Controls(0) Dim zip As textbox = E.Item.cells(7).Controls(0) Dim phone As textbox = E.Item.cells(8).Controls(0)'''''' the following line declaring the descript var is the line of the error Dim descript As textbox = E.Item.cells(9).Controls(0) Dim web As textbox = E.Item.cells(10).Controls(0) Dim email As textbox = E.Item.cells(11).Controls(0) Dim datesold As textbox = E.Item.cells(12).Controls(0) Dim dateexpire As textbox = E.Item.cells(13).Controls(0)  Dim strUpdateStmt As String        strUpdateStmt =" UPDATE CPAs SET" & _        " company = @company, address = @address, city = @city, state = @state, " & _        "county = @county, zip = @zip, phone = @phone, descript =@ descript, " & _  "web = @web, email = @email, datesold = @datesold, dateexpire = @dateexpire" & _  " WHERE cpaID = @cpaID" conn = New SqlConnection(strConn) MyCommand = New SqlCommand(strUpdateStmt, conn) MyCommand.Parameters.Add(New SqlParameter("@company", company.text)) MyCommand.Parameters.Add(New SqlParameter("@address", address.text)) MyCommand.Parameters.Add(New SqlParameter("@city", city.text)) MyCommand.Parameters.Add(New SqlParameter("@state", state.text)) MyCommand.Parameters.Add(New SqlParameter("@county", county.text)) MyCommand.Parameters.Add(New SqlParameter("@zip", zip.text)) MyCommand.Parameters.Add(New SqlParameter("@phone", phone.text)) MyCommand.Parameters.Add(New SqlParameter("@descript", descript.text)) MyCommand.Parameters.Add(New SqlParameter("@web", web.text)) MyCommand.Parameters.Add(New SqlParameter("@email", email.text)) MyCommand.Parameters.Add(New SqlParameter("@datesold", datesold.text)) '', dateexpire =@dateexpire MyCommand.Parameters.Add(New SqlParameter("@dateexpire", dateexpire.text))  MyCommand.Parameters.Add(New SqlParameter("@cpaID", e.Item.Cells(1).Text )) conn.Open() MyCommand.ExecuteNonQuery() MyDataGrid.EditItemIndex = -1        conn.close BindDataEnd Sub'''''''''''''''''''''''''''''netsports

View Replies View Related

Specified Cast Is Not Valid - Txn Replication Snapshot Agent Error

I have set up transactional replication with immediate updating subscriptions on my test SQL 2005 servers. One of the snapshot agents failed with "Specified cast is not valid" error message. I have created the subscription for that database by initializing it from backup instead. I would like to find out why this snapshot agent is failing and how to fix it.
SQL Server 2005 Standard Edition SP2, Windows Server 2003 R2 64-bit, SP2.


Error messages:

Source: Microsoft.SqlServer.Replication
Target Site: Int64 ExtractInt16RangeHighKeyValueAsInt64(StepInfo)
Message: Specified cast is not valid.
Stack: at Microsoft.SqlServer.Replication.Snapshot.SqlServer.ArticleBcpPartitioningResolver.ExtractInt16RangeHighKeyValueAsInt64(StepInfo stepInfo)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.ArticleBcpPartitioningResolver.InterpolateStepInfos(Int32 targetNumberOfPartitions)
at Microsoft.SqlServer.Replication.Snapshot.SqlServer.ArticleBcpPartitioningResolver.ResolveArticleBcpPartitioningUsingKeyDistributionHistogram(BaseArticleWrapper article, Boolean usePrimaryKeyOnly)
at Microsoft.SqlServer.Replication.WorkerThread.NonExceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.AgentCore.BaseAgentThread.AgentThreadProcWrapper() (Source: Microsoft.SqlServer.Replication, Error number: 0)
Get help: http://help/0

Thanks for your help.

View Replies View Related

Got 'Specified Cast Is Not Valid' Opening Mining Model Viewer

Hi all

I have a problem I created mining model with Microsoft Sequence Clustering algorithm, when I tried to open it in viewer, I got an error 'Specified cast is not valid'.
After looking in SQL profiler I see, that viewer execute MDX query to get node attributes of modelELECT FLATTENED TOP 1 (SELECT [ATTRIBUTE_NAME] FROM [NODE_DISTRIBUTION] WHERE VALUETYPE = 1) FROM [Dating Usepath].CONTENT WHERE NODE_TYPE = '13' - this query returns empty result set.

I modified it and got the following:

Op To Immo    5
Rating Range    5
Action    5
Age Int    5
Op To Immo    5
Rating Range    5
Action    5
Age Int    5
Op To Immo    5
Rating Range    5

It's only the bottom of resut set, but it shows that the names of attributtes actually empty.

What I did wrong, and what I possible change in the model to view Cluster diagram correctly.

P.S. When I created model on the same data with Microsoft Clustering algorithm everything is ok.

View Replies View Related

Cast From String &&"1/31/2007&&" To Type 'Date' Is Not Valid.


I hv a major problem, I have a working Asp.NET application running in Win2003 without any Service packs. My SQL server is also running on win2003 in other machine. This setup is working perfectly without any probelm.

Now I have a new server with Win2003 R2 SP2 and I installed my asp application to this server and now I getting following error in all the pages which are having date fields. Eventhough I enter "31/01/2007" it gives me following error

Cast from string "1/31/2007" to type 'Date' 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: Cast from string "1/31/2007" to type 'Date' is not valid.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:

[InvalidCastException: Cast from string "1/31/2007" to type 'Date' is not valid.]
Microsoft.VisualBasic.CompilerServices.DateType.FromString(String Value, CultureInfo culture) +162
Microsoft.VisualBasic.CompilerServices.DateType.FromString(String Value) +53
Microsoft.VisualBasic.CompilerServices.DateType.FromObject(Object Value) +156
SharjahVisa.SearchList.Page_Load(Object sender, EventArgs e) +526
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +750

Version Information: Microsoft .NET Framework Version:1.1.4322.2379; ASP.NET Version:1.1.4322.2379


All my three servers are set to  dd/MM/yyyy format in reginal settings with UK as country.  I tried inserting culture="en-GB" in globalization part of the webconfig also. still it gives me same error. But the same application working fine in old server. can some one pls help me on this error. I  am not a developer.-- sysadmin



View Replies View Related

Cast From String &"&" To Type 'Date' Is Not Valid

Hello, I am using an SQLDataReader called DrCoInfo to check if any matching records exist in a table.

My code is as such:

Line 119:DrCoInfo = comm1.ExecuteReader()
Line 120:While DrCoInfo.Read()
Line 121: If DrCoInfo("DateOfMan") <> "" Then
Line 122: BValid = True
Line 123: End If

However, it fails to work with the message :System.InvalidCastException: Cast from string "" to type 'Date' is not valid.

Any suggestions? Thanks.

View Replies View Related

Using Cast


I am trying to use the Cast keyword with little luck. At the end of the select below, I want the last columm (Average) to be a numeric field (numeric 7,5). I just can't seem to get cast working.

Any thoughts,



Insert [Bankers_Scores_solic] ([ID], EqorMA, Region, Period,IBFirm, UndNeed ,QualIdea ,ProfAggr ,SpKnow ,negskill ,Perchem, RepPri, Repcomp, TranType, [Count Of Scores], [Sum of Scores], Average)
Repcomp ,
(Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
AS [Count Of Scores]
+ (ISNULL([Perchem],0))
AS [Sum of Scores]
(ISNULL([Perchem],0)) +
/((Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
AS Average numeric(7,5))
FROM tmp_Solicitation_Scores

View Replies View Related


I have

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 ?

View Replies View Related


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])


View Replies View Related

Need Help With A CAST

I am having a little trouble with the CAST in my SELECT statement below.  Any help is greatly appreciated.
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'.

View Replies View Related

DDL Is Not Valid

hello,everybody ,i use the AnalysisServicesExecuteDDLTask ,but it dap an error "DDL is not valid",i don't konw what is the problem and how to solve?thank you!

View Replies View Related

If / IIf - Cast Exception

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 

View Replies View Related

Cast Or Convert

 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 Replies View Related

Cast Problem

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 !!!

View Replies View Related

Using CAST From Varchar To Int

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

View Replies View Related

Varbinary Cast

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 Replies View Related

CAST Command

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

View Replies View Related


SELECT ISNUMERIC('. ')1SELECT CAST('. ' AS [insert any numeric type])Syntax error converting the varchar value '. ' to a column of data type[insert any numeric type].Any thoughts as to why SQL Server 7.0 considers '. ' to be numeric, yet can'tconvert it to any numeric data type? The BOL even has this to say: "ISNUMERICreturns 1 when the input expression evaluates to a valid integer, floatingpoint number, money or decimal type; otherwise it returns 0. A return value of1 guarantees that expression can be converted to one of these numeric types."An application that has worked for years, failed when a field of type CHAR(3)(that should be an area code) contained a period only. I have error-checkingthat tests ISNUMERIC on that field, and if it fails, alert accordingly. If itpasses, then convert it to a number, and compare it to acceptable ranges forArea Code. (200 < AC < 999)oops...

View Replies View Related

Check If Cast Is Possible

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)

CAST(PSPR_temp.DATA_BORN AS smalldatetime), PSPR_temp.SEM_POL, PSPR_temp.XAR_JT, PSPR_temp.CHILDREN,

PSPR_temp.SV_ROD1 + PSPR_temp.SV_ROD2 AS Expr1, PSPR_temp.REGION, PSPR_temp.STUD_FML,

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.

View Replies View Related

CAST Statement

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.

View Replies View Related

Cast Errors

Update and insert procedures to datetime fields in our database (6.50.416 on NT4.0 SP6) cause sporatic casting error problems. The operation might work one day but the next we get the error! Can anybody explain this behavior?

Typical procedure:

Create Procedure uPolicyDriver
@DOB datetime

update Driver
set Birthdate = @DOB

return (0)
The error is as follows:

Server Error

22005[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

The table we try to update is as follows:

BirthDate datetime NULL ,
ClientID int NOT NULL ,
DateLicensed datetime NULL ,
DriverID int IDENTITY (1, 1) NOT NULL ,
DriverLicense varchar (20) NULL ,
DriverNumber tinyint NULL ,
DriverTraining tinyint NULL ,
Employer varchar (20) NULL ,
EmployerAddress varchar (26) NULL ,
EmploymentStatus tinyint NULL ,
LastMVR datetime NULL ,
Level1 bit NOT NULL ,
LicenseClass tinyint NULL ,
LicenseCountry tinyint NULL ,
MaritalStatus tinyint NULL ,
MVRReceived bit NOT NULL ,
MVRRequested bit NOT NULL ,
NameID int NULL ,
Occupation varchar (22) NULL ,
PolicyID int NOT NULL ,
ProvLicensed varchar (20) NULL ,
RelationshipToApplicant tinyint NULL ,
YearsCommonLaw tinyint NULL ,
YearsLicOC int NULL

View Replies View Related

Help With Conver/Cast

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 Replies View Related


I 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

View Replies View Related

Cast And Round

I am using a statement in which a where clause has a statement like this

Where ..
and cast(round(colname,1) As Numeric(9,1)) =>9.5

When i execute this query i get this error

Server: Msg 8114, Level 16, State 5, Procedure sp_rpt_get_count_hb_great_9andhalf, Line 22
Error converting data type varchar to float.

Any help is appreciate

View Replies View Related

Cast Formula

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

View Replies View Related

CAST Problem

I have a problem with CASTING

I have a CASE stmt, i check a var that is an int in the db, but based on values i wanna display strings.


CASE var1
WHEN var1 = 51 THEN 'Level 1'
WHEN var1 = 52 THEN 'Level 2'
ELSE var1
END as x

This gives me Conversion failed when converting the varchar value 'Level 1' to data type int.

Fair enough
but when i try to cast it it still gives me the same error ?

CASE CAST(var1 as varchar(10))
WHEN var1 = 51 THEN 'Level 1'
WHEN var1 = 52 THEN 'Level 2'
ELSE var1
END as x

View Replies View Related

Error When Using Cast

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.


Incorrect syntax near the keyword 'WHERE'.

View Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved