Quick Casting Problem...

Feb 25, 2005

Can anyone please telkl me what is wrong with this portion of a SQL statement. I have been racking my brain over this and can't seem to get it right...

(CASE playerstats.fgm WHEN 0 THEN 0 ELSE (cast(100.00 * ((cast(SUM(playerstats.fgm)) as Decimal(8,2))/(cast(SUM(playerstats.fga)) as Decimal(8,2)))) as decimal(8,1))) AS fgp

I had it working fine, but when playerstats.fgm was a 0 then I got a divide by 0 error. This was the code when it was working ok as long as no one entered a 0 for fgm

(cast(100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) as decimal(8,1))) AS fgp

All I am trying to do is find a percentage... when playerstats.fgm = 0 then the percentage will be 0.


Any help will be much appreciated!!! :confused:

View 1 Replies


ADVERTISEMENT

Data Casting, Casting Operations

Jul 23, 2005

Does anybody know how could I calculate the new date(adding @c to @bor subtracting @b to @c), having for example a declaration like this:DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @b='3.04.04';SET @c=6and to calculate the number of days between two dates with this kindof declaration(@a-@b or @b - @a):DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @a='12.2.04';SET @b='3.04.04';Thanks in advance.

View 1 Replies View Related

Casting Help

Aug 25, 2004

Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.

Thanks
Sam "O"



cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol)
SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat,
(cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) +
cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) +
cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) +
cast(sat as numeric(4,2))) as totcol from
" & strTableName, conTimeCard)

intUpdateCount = cmdInsert.ExecuteNonQuery()

View 4 Replies View Related

Casting

Jul 3, 2007

Hello,

I'm new to SQL Server and I have to finish a VB.Net program from an employee that has left the company.

I have a table with column 'vanafdatum' witch has datatype char but they stored only dates in this column ( like this: 13/09/2006).

When I try to run the next querie I receive an error message "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".

This is the querie I try to run


SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum
FROM tarieven
WHERE (vanafdatum <
(SELECT getdate()))


Is there any way I can cast the char datatype to a datetime datatype without getting this error.
I allready tried to change the data type from char to datetime, but doesn't work either.
A possible solution is to erase the column an manually fill in all the fields again in the data type datetime, but that seems a little stupid to do.
So I wonder if there is a solution that can change the data type for all rows in the database without losing any data.

View 5 Replies View Related

Casting...

Aug 30, 2006

I've got an ftp task that will be downloading a couple of files each night. today they're called

blah20060830blah

the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.

Thank you

View 23 Replies View Related

SQL CE Casting

Feb 6, 2007

Hi,

I would like to know if casting is supported in SQL Server Compact Edition. I get an error when i attempt to cast a datetime or bigint value to nvarchar or ntext. CAST(datetime AS nvarchar(15))

Does anybody knows if its supported or how could i cast values?

Thank you

View 1 Replies View Related

SQL Casting Question

Oct 10, 2005

Hi,I've come across a problem that requires i cast a string to a date, had a go but can't get it to work properly.SELECT EventID, EventName, EventShortDesc, EventLink, EventStartDateFROM dbo.tbl_EventWHERE (CAST EventStartDate AS DATE) > GetDate()ORDER BY EventStartDate DESCBasically i only want events which haven't expired (were before the current date) to be returned. EventStartDate is held as an varchar(10) therefore needs to be cast before i can compare. Is this the best way or is there a better one? If it is how do i get it to work?Any help would be great thanks,drazic19

View 2 Replies View Related

Casting '01/07' To A Date

Feb 18, 2008

Hi All

My brain has truned to mush at the moment.
:(

I want to sort on a char(5) column with data in it that looks like

01/07
05/02
06/01
12/01
07/98

so it comes out in the human date order.
e.g

07/98
06/01
12/01
05/02
01/07

--
David

View 6 Replies View Related

Casting, Converting

Nov 13, 2007

Hi all,

I'm trying Insert data using INSERT INTO kind of like below

INSERT INTO table1(

col1
col2
col3
col4)
SELECT

col1
col2
col3
col4
FROM

Server.DB.table2


say, col3 from table 1 is numeric (28,8) and col3 from table 2 is float.

how do I cast this so it works??

thank you!

View 9 Replies View Related

Multi Casting

Apr 6, 2006



Just a question about multi casting.

If I create a copy of a data set using a multi cast, do operations on one of the output sets effect the other output set?

For example, in one ouput set I'm setting a column to NULL - this is actually updating the other set as well. Is this meant to happen?

Thanks!!

View 1 Replies View Related

Casting Not Working?

Jun 18, 2007

Can anyone see what's wrong with this transformation?

((DT_STR)([IN-DLN]))

I'm trying to cast DLN as a string, but I keep getting a parsing error in the derived column tranformer...

Thanks!

Jim Work

View 7 Replies View Related

Casting Decimal Number

May 12, 2006

Hello,
When I declare a VB variable Dim s as Decimal,
I want to cast d like this : 1452,4141,0045,47756544,04
Only with to digits after the ","
How can I perform this

View 5 Replies View Related

Unexpected Casting With Sum && Coalesce

Sep 28, 2006

If I have an SQL query which returns an aggregate of several decimal fieldslike so:(sum(COALESCE(myDecimal1, 0)+sum(COALESCE(myDecimal2, 0)+sum(COALESCE(myDecimal3, 0)) as MyTotalI get an rounded integer in MyTotal.However, if I do the following:sum(COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)+COALESCE(myDecimal1, 0)) as MyTotalI get a (proper) decimal value.Does anyone know why the first case returns an Integer?- Don

View 4 Replies View Related

Transact SQL :: Casting To Same DataType

Jun 10, 2015

Biz talk server executes every query twice, first with FMT only to get metadata.If metadata changed, it will produce error.Now, i have query like:

SELECT col1, col2, col3 INTO #tmp FROM dbo.myTable

How SQL decide the type of column in #tmp? Does it create column of the same type as it is in dbo.myTable or its decision is based on result?For example, col1 is varchar(20), but col1 in #tmp will be varchar(200) or even nVarChar.Col2 is INT, but result is for example low number(like, 1,2,3,) - is it possible that col2 in #temp table will be smallint or tinyint even if col2 in myTable is INT.If that is true one way to overcome that will be to explicitly CAST all columns to proper data type. Is there any performance impact of casting: CAST(col1 as VARCHAR(20)) if col1 is already varchar(20) in dbo.myTable?

View 9 Replies View Related

Casting/Convert Is Not Working

Jan 7, 2008

I am trying to Convert integer into float value by using
CAST(value AS float).
But seems it doesn't work. I also used

CONVERT(float, value)
but it is alos not working properly.

Any Idea?


View 5 Replies View Related

CASTING Statement For A Date

Sep 6, 2006

I am selecting older legacy data from an AS400 mainframe that we still use. I am fairly new to constructing T-SQL statements so I hope I am doing this correctly. There is a table from the AS400 that has been setup with a field for TMONTH, TDAY, and TYEAR. Instead of having one field for a date, for some reason years ago this was set up this way.

I now have this statement in my SELECT statement and it is not working:

WHERE (CAST(OWNR.TMONTH + '/' + OWNR.TDAY + '/' + OWNR.TYEAR AS DATETIME) >= @startdate)

I am not getting a syntax error, however I am getting "Error Converting data type varchar to numeric. These fields on the AS400 are set up as numeric fields.

What do I need to do differently? Should I use a CONVERT instead and if so, how would I structure that statement.

Thanks for the help


View 3 Replies View Related

Disable Implicit Casting

Oct 10, 2006

Is there a way do disable/disallow implicit casting in Sql Server 2000?

Say for example:

if ' ' = 0 select getdate()


This wil print out the current date and time.

But ' ' (a string, varchar, whatever) is not the same type as 0 (say, int). Implicit casting is nice, but is there also an off switch?

View 2 Replies View Related

Implicit Casting And UNPIVOT

Jun 29, 2007

Hi all,



I am running into a problem which seems to indicate SQL Server 2005 is pretty aggressive with its implicit conversion which makes UNPIVOT apparently unusable for my current task of creating attribute/value pairs of strings for each of the records unpivoted around the primary key.



WITH cte as

(

SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate

)

SELECT

[Code]

,[Attribute]

,[Value]

FROM

cte

UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;



Msg 8167, Level 16, State 1, Line 1

The type of column "CreatedBy" conflicts with the type of other columns specified in the UNPIVOT list.



The same result shows up if I use an inner SELECT instead of the CTE:



SELECT

[Code]

,[Attribute]

,[Value]

FROM

(

SELECT 1 as Code, '2' as Status, 'Some Guy' as CreatedBy, Convert(varchar(19), getdate(), 120) as CreatedDate

) cte

UNPIVOT([Attribute] FOR [Value] IN ([Status],[CreatedBy],[CreatedDate])) as U;



It seems to me that some arbitrary decision about what column is processed first is made and the process fails as soon as a column is met which is not of the same datatype. I also think it is interesting that the engine will implicitly cast from varchar to DateTime but not the other way around.



Any thoughts on how I can get around this?



Thanks in advance,

Calvin

View 1 Replies View Related

Datatype Casting In Derived Column

Apr 21, 2006

Here is my expression in a derived component:

"Failed insert into PONL_WELL. WELL_NO=" + (DT_WSTR,10)PROP_NO

PROP_NO comes from ms sql server , and the derived component datatype for this column is DT_WSTR.

The destination will be ms sql server, and i have a data conversion after the derived component to cast from DT_WSTR to DT_STR.

However, the derived component failed everytime giving me

Error: 0xC0049064 at Load Ponl_Well, Derived Column [1342]: An error occurred while attempting to perform a type cast.

Anyone know how i can eliminate the data conversion component and just do my string and column concatenation in the derived column and have it output as DT_STR?

View 3 Replies View Related

Casting DT_WSTR To Integers In SSIS

Jul 5, 2007

Hello, all.



Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?



(DT_UI4) (SUBSTRING(@[User::FullDataPath], LEN(@[User:ataPath]) + 1, 2))



I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.



Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.



Thanks for any help.

View 7 Replies View Related

Type Casting Varchar As Time

Apr 16, 2007

Hi.



Current development setup: Visual Studio 2005 (C# language), SQL Server 2005 Express Edition.



I am at this point changing some of my application. It stores a lot of information for times of trains. I decode a file which is text based and my times are stored in varchar(4) columns with the format "HHmm".



I am now at a stage where I need to add values to these times and perform a search based on two time values (a minimum time and maximum time).



I really need to be able to add another varchar(4) value to the times stored in my database (also varchar(4) - may look at this again in future). I have absolutely no interest in storing seconds, modifying the database to include the time colon, nor am I interested in the date portion of a time string, only the 4 digits representing the hours and minutes.



Can this be done easily in SQL server? For example:



varchar(4) of 0445 + varchar(4) of 0027 should result in the time 0512.



Similarly a varchar(4) of 2358 + varchar(4) of 0015 should result in the time 0013.



This has to be very quick to execute.



Thanks for any help you can offer!



Sean



View 1 Replies View Related

Error Casting String To Date

Oct 24, 2007

I have a Foreach loop container that I'm using to loop through a directory and copy files to a new directory. The file name contains the date and I need to compare the value to the current date to determine if the file should moved. I'm having a problem converting the string to a date.

I have a FileName variable of type string that is in this form: 200710221200_FileName_7001.log

I have a FileDate variable of type datetime that I'm trying to use the following expression on:
(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))

Which is giving me the following error:

The expression "(DT_DBDATE)(SUBSTRING(@[User::FileName] , 1,4) +"-"+ SUBSTRING(@[User::FileName] , 5,2) +"-"+SUBSTRING( @[User::FileName] , 7,2))" has a result type of "DT_DBDATE", which cannot be converted to a supported type.

From the help files, it appears to me that the DT_DBDATE cast uses the form of yyyy-mm-dd. I'm not sure what I'm doing wrong. Any help is greatly appreciated.

Thanks!


Wendy Schuman

View 12 Replies View Related

Casting Or Converting Smallint Datatype To Datetime

Sep 8, 2007

A SQL Server 2005 db has three date related-columns (MonthGiven,
DayGiven, YearGiven) each as smallint datatype.  I would like to
create a DocDate column (datetime datatype) that combines the data from
the three existing date-related columns.  I have tried casting and
simple concatentation without success.  ALTER TABLE Details ADD DocDate DateTime NULL

UPDATE Details SET DocDate = CAST(MonthGiven AS DateTime)+ '/' + CAST(DayGiven AS DateTime) + "/" Cast(YearGiven As DateTime)
I think I need to be doing a Conversion instead of casting but
have been unable to implement info I have found in the SQL Server
Developer Center in my situation.

View 11 Replies View Related

Changing Date Format And Casting As Varchar?

Jul 21, 2014

My current Query takes the DATE value stored in P.CreatedDate and makes it VARCHAR, then stores it in the table. I need to format this to return YYYYMMDD. How would I go about this?

Current Code:

Code:
CAST(P.CreatedDate AS VARCHAR) AS DateEntered,

View 3 Replies View Related

CASTing A Datatype Returned By CASE Statement

Apr 28, 2004

I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.

ddave


SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
END

View 7 Replies View Related

Converting/Casting Strings Into Datetime Datatype

Aug 10, 2007

Hello,

I have a varchar column that inludes dates in the following fomat: 03032007? When I try to cast this to datetime, I keep getting "Arithmetic overflow error converting expression to data type datetime." error. Maybe someone has some ideas how to handle this?


Thanks!

View 4 Replies View Related

Transact SQL :: Casting A Variable To Nested Table?

Sep 24, 2015

Is there any way to convert a bind variable, which is a list of integer or string values, to nested table in MS SQL Server. I am looking for something like

CAST in Oracle, which converts a varray type column into a nested table. Do we have something like this in SQL Server.

in Oracle:

SELECT CAST(s.addresses AS address_book_t)
FROM states s
WHERE s.state_id = 111;

I have read about Table valued Parameter, but I am looking for another solution. 

View 4 Replies View Related

Rounding Consistency When Casting -- Datetime Type

Mar 5, 2008

I would expect this query:



select

case when cast(DT as int) = cast(cast(DT as float) as int)

then 'Consistent'

else 'Inconsistent'

end

from (select cast('27 Jan 2008 13:00' as datetime) as DT) X


to always return 'Consistent'. My expectation is not satisfied; the question is, is it unfounded?

View 13 Replies View Related

How To Fix The Deficit Occured By The Some Converting Or Casting Operation In Sql.

Mar 24, 2008

TotMatch =58
ToNOMatch=1


select @Agg = Cast(cast(cast(Max(TotMatch) as decimal) / cast((Max(TotNoMatch) + Max(TotMatch)) as decimal)* 100 as int) as varchar) + '%' FROM #Rpt;
select @Agg1= Cast(cast(cast(Max(TotNoMatch) as decimal) / cast((Max(TotNoMatch) + Max(TotMatch)) as decimal)* 100 as int) as varchar) + '%' FROM #Rpt;


Now what i am doing finding the percentage of total match
and then
finding the no mathc and percentage of both.
but when i get the answr.

@Agg=98
@Agg1=1.

so there is deficit of 1. how i can fix that.

View 4 Replies View Related

Casting Float Output Param Throws An Exception.

Jan 9, 2004

I keep getting an exception when trying to cast an output param as a float type. the SPROC is marked as float OUTPUT and the Cost column in the database is a float type as well. And there are no nulls in the cloumn either. here is how my code looks:


SqlParameter prmCost= new SqlParameter("@Cost", SqlDbType.Float,8);
prmCost.Direction=ParameterDirection.Output;
cmd.Parameters.Add(prmCost);

//...blah blah blah

//invalid cast gets throw on here (it happens with all my float types)
productDetails.Cost=(float)prmCost.Value;




Any suggestions as to what I am doing wrong?

View 3 Replies View Related

T-SQL (SS2K8) :: Getting Undesirable Trailing Zeros After Casting To Decimal

Sep 7, 2014

The following line of SQL returns the first screen shot below.

((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS 'Gross Profit %'

I now want to limit the decimal places to two (2), and one might think that using a CAST operation here is the solution, as follows.

CAST(((T0.TotalSales - ISNULL(T1.TotalSales, 0)) - (T0.StockValue - ISNULL(T1.StockValue, 0))) / (T0.StockValue - ISNULL(T1.StockValue, 0)) * 100 AS decimal(15,2)) AS 'Gross Profit %'

However in actuality I am still seeing too many decimal places, but the extra ones have changed to ZEROS!

Ideally I would like to have no extra trailing zeros. How to achieve this?

View 2 Replies View Related

SQL Server 2012 :: Premature Casting On 0 Rows Insert

Feb 28, 2015

I have encountered some weird behaviour. Code that has been working for "eternities" suddenly started to fail. I couldn't recreate it on any other machines.

I have written a sample script to illustrate the issue:

CREATE TABLE #t_parcels (parcel_id INT, current_pos NUMERIC(28, 0), end_pos NUMERIC(28, 0))
CREATE TABLE #t_orders (outorder INT, parcel_id INT)
CREATE TABLE #t_missing_parcels (parcel_id INT, diff INT)

INSERT INTO #t_parcels (parcel_id, current_pos, end_pos)
SELECT1, 100000000000.0, 900000000000.0

[Code] ..

The last insert crashed with :"Arithmetic overflow error converting expression to data type int", even though there are no rows that satisfies the condition!

This is due to "diff" column having wrong datatype, BUT, the insert had no hits in the database. So how can inserting 0 rows crash with incorrect datatype?

I even copied the select so it was ran before the insert, in in that case, the SELECT completed successfully.

When i changed datatype in the table, the error went away, but I'm still curious what led to the error.

View 9 Replies View Related

Casting An In-memory Resultset To A DataSet For Use In XML File Generation

Sep 13, 2006

I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).

I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.

I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.

Public Sub Main()

'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)

Dim mySet As DataSet

mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))

mySet.WriteXml("C: est.xml", XmlWriteMode.WriteSchema)

Dts.TaskResult = Dts.Results.Success

End Sub

I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?

Thanks

View 2 Replies View Related







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