if I 'print' a MONEY value, or cast a MONEY variable to VARCHAR, it automatically rounds it to two decimal places. Maybe that's a built-in convenience, but I'd like to make it not do that.My workaround right now is to first cast my MONEY variabled to DECIMAL(30,4), and then cast the result to VARCHAR, but I'd like to avoid that step if possible.Consider the following
query:DECLARE @UnitCost MONEY SET @UnitCost = .0167 SELECT @UnitCost, CAST(@UnitCost AS VARCHAR(30)), CAST(CAST(@UnitCost AS DECIMAL(30, 4)) AS VARCHAR(30)) - - Results in: 0.0167, 0.02, 0.0167
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.
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)
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.
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.
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?
I have a pulldown menu which has like 4 options producta productb productc and all I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC and I am trying to use coalesce but unable to get my result. I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas. If I do the following with no max funciton, i see all the values but i just want max from each branch. DECLARE @buildList varchar(100)select @buildlist=COALESCE(@buildList + ', ', '') + convert(varchar(10),build) from results where branch in ('ProductA','Product B','ProductC') select @buildList
I have a stored procedure which receives a dynamically built WHERE clause. This is then appended to the sql query within like....'select * from table' +@where_clause. I know that I am possibly leaving myself open to sql injection so I wanted to find an alternate way of handling this. I stumbled across an article which speaks of using COALESCE as a way to sidestep using dynamic WHERE clauses....http://www.sqlteam.com/article/implementing-a-dynamic-where-clause In my application the user can enter 1-to-many textboxes as search criteria. What I have been doing is a series of IF statements to determine if each textbox is populated or not and build the WHERE clause accordingly.If tx_lastname.Text <> "" Then If (InStr(sqlwhere, "where")) Then sqlwhere = sqlwhere & " AND lname like '" & tx_lastname.Text & "'" Else sqlwhere = " where lname like '" & tx_lastname.Text & "'" End If End If If tx_firstname.Text <> "" Then If (InStr(sqlwhere, "where")) Then sqlwhere = sqlwhere & " AND fname like '" & tx_firstname.Text & "'" Else sqlwhere = " where fname like '" & tx_firstname.Text & "'" End If End IfAnd so forth. But the above article seems to insinuate that I provide a static WHERE clause like this...'select * from table WHERE LNAME = COALESCE(@lname, lname) and COALESCE(@fname, fname). And this would handle it.Have any of you ever used this before? This is my first question. My other question is could this particular method be made compatible to fit with the LIKE operator?My user needs to be able to search based on close matches. For instance, if they enter 'JOHN' in the last name field, the results should contain 'JOHN', 'JOHNSON', 'JOHNS', etc.Any help would be appreciated.
A few people have mentioned that i should use coalesce in the following statement. the problem is i don't know where i should be using itCan someone show me where i should place it? 1 SELECT (SELECT Location 2 FROM Location_Table 3 WHERE (Property_Table.LocationID = LocationID)) AS Location, 4 (SELECT TypeOfProperty 5 FROM Type_Table 6 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty, PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms 7 FROM Property_Table 8 WHERE (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) OR 9 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) OR 10 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) OR 11 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) OR 12 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price <= @MaxPrice) OR 13 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR 14 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price <= @MaxPrice) OR 15 (NULLIF (@MinPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR 16 (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR 17 (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR 18 (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR 19 (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice)
I have inherited a db with slowness claims. Last week at a MS seminar where independent SQL Consultant gave presentation on performance gotchas. One of his top 5, do not use coalesce on joins and where clause. Of course it is all over this db. Looking at below was this a bad approach?
WHEREcoalesce(PM.ALTKEYDOC,'x') = coalesce(@AK,PM.ALTKEYDOC,'x') AND coalesce(PM.FIRSTNAME,'x') LIKE coalesce('%' + @FN + '%',PM.FIRSTNAME,'x') AND coalesce(PM.LASTNAME,'x') LIKE coalesce('%' + @LN + '%',PM.LASTNAME,'x') AND coalesce(PM.SEX,'x') = coalesce(@SX,PM.SEX,'x') AND coalesce(PM.BIRTHDATE,'1/1/1900') = coalesce(@BD,PM.BIRTHDATE,'1/1/1900') AND coalesce(PM.DIVISION,'x') = coalesce(@DI,PM.DIVISION,'x') AND PM.STATE = @STATE ORDER BY LASTNAME
I have a piece of a store procedure I don't quite understand, as follows:
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId FROM utDeal d WITH (NOLOCK) INNER JOIN utContactIllustration ci WITH (NOLOCK) ON ci.IllustrationId = d.IllustrationId WHERE d.DealReference LIKE (COALESCE(@DealReference,'%'))
What exactly is the COALESCE function doing here with the parameter?
How would i use a coalesece on this function to get null. if i use coalesce(xxxxx,0). If there is nothing in there it returns a blank space but i need to put null in there
cast([DPVisionPlan] as nvarchar(255)) [DPVisionPlan],
I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.I came across a piece of code posted in the forum about the different uses of coalesce.
use adventureworks DECLARE @DepartmentName VARCHAR(1000) SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';' FROM HumanResources.Department WHERE (GroupName = 'Executive General and Administration')
I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.
Below is my sample:
SELECT *
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY q.StudentID
Having
(SUM(Coalesce(q.AbsenceValue),0) = 0.00)
COALESCE(SUM(q.AbsenceValue) = 0.00,0)
I have tried using the coalesce statement a couple of ways with no resolution, pls help!!
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
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
Hi everybody, I have a stored procedure that creates some temporary tables and in the end selects various values from those tables and returns them as a datatable. when returning the values, some fields are derived from other fields like percentage sold. I have it inside a Coalesce function like Coalesce((ItemsSold/TotalItems)*100, 0) this function returns 0 for every row, except for one row for which it returns 100. Does that mean for every other row, the value of (ItemSold/TotalItems)*100 is NULL ? if so, how can I fix it ? any help is greatly appriciated. devmetz
Hi All I have a problem in making out why Coalese is considered to be better than ISNULL .According to my investigation Coalesce Returns the data type of expression with the highest data type precedence.If for eg I have declared that I want to return the value only upto 3 char then why will I use Coalesce and override my requirement.Anyone with clear concept about this plz explain. DECLARE @v1 VARCHAR(3)DECLARE @i1 INT SELECT ISNULL(@i1, 15.00) /2, COALESCE(@i1 , 15.00) /2, ISNULL(@v1, 'Teaser #2'), COALESCE(@v1, 'Teaser #2') The result will be 7 7.500000 Tea Teaser #2
my question is about using coalesce function in SQLServer. This function brings up the first not null value. my problem is i cannot get the corresponding field name. this is what i use
SELECT COALESCE(Stop1, Stop2, Stop3, ...., Stop10) FROM ... WHERE ProjectID = #URL.ProID#
it returns,
(no column name) ---------------- 1 somebody@somthing.com
the fields in the DB goes like stop1, stop2, stop3,... so, i need to get which stop i am .thanks in advance.
from one of my solution, i havent had time untill now to ask detail what is this coalesce and nullif? Is nullif just like the isnull function? while coalesce is to replace null? can someone explain base on this eg??
d.LocID>= coalesce(nullif(@LocFrom, ''), d.LocID) and d.LocID<= coalesce(nullif(@LocTo, ''), d.LocID) and
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
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.
