February 18, 2005 at 12:09 pm
The results I have outputted currently are:
07/07/2004 25.00 25.00
What I want though is for them to come out as:
07/07/2004 25 25
The string that I'm making is derived from 3 fields which I am concatenating together.
sold_date (type datetime)
amountsold (type money)
amountpaid (type money)
What I need is an update query that will concatenate the 3 fields together as a string, but so far am running into a stumbling block in that nothing I do is working so far. Anyone got an idea for me?
-Thgamble1
February 18, 2005 at 12:21 pm
I would cast the value to an int then to varchar for the string concat:
cast(cast([money] as int)as varchar(2))
..probably faster more efficient ways but this should give you the idea.
February 18, 2005 at 12:30 pm
maybe a left() operation could be faster... but the question is : Is it always ok the truncate the decimal part or do you need to keep it if it's not all zeros?
February 21, 2005 at 2:40 am
I'm sure you have pretty good reasons for this intentional denormalization, don't you?
Anyway, as Remi already mentioned, if you don't care about truncation and rounding isn't an issue either, see if this helps
DECLARE @m1 MONEY, @m2 MONEY
SELECT @m1 = 25.25, @m2 = 24.95
SELECT STR(@m1,2)+' '+ STR(@m2,2)
------
25 25
(1 row(s) affected)
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
February 21, 2005 at 7:09 am
What I'm trying to do is update a field in a database to display a concatenated string to my agents for a sales type campaign that we are doing.
The information that is displayed is simply "Pledge Date" / "Pledge Amt" / "Paid Amt" so that my agents can know better how much to try to pitch the customers. It's not used for anything other than display, and it's always in whole dollars rather than decimals. The problem though is that while most of the time the max amount displayed is <100 there are occassions when it is greater, and thus I can't simply put in a substring type update to only give me the first 2 characters.
And when I try to modify the select statements from Frank and others, I am doing something wrong because I keep getting nothing but the database trying to concatenate the 3 seperate fields into a datetime format rather than strictly characters put together.
My exact update string is:
Update L set L.[Pledge Line 1] = ((convert(varchar(10),[Invoice Date],101)) + ' ' + (convert(varchar(10),[Pledge 1])) + ' ' + (convert(varchar(10),[Amt Paid])))
from [Lead_Management_Preparation Table] L where L.[Invoice Date] <> '1/1/1900'
February 21, 2005 at 7:19 am
Have you try with STR() yet?
This works for me:
ALTER TABLE Orders ADD testME VARCHAR(30)
GO
USE NORTHWIND
GO
UPDATE Orders SET testME =
CONVERT(VARCHAR(10), OrderDate,101) +
' ' +
STR(Freight,2 ) FROM Orders WHERE OrderDate = '19960704'
SELECT testME FROM Orders
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply