Formating Numbers, How Do I Format:
Dec 10, 2007a number like 1,234,456.82 ?
View 13 Replies
ADVERTISEMENT
Formating Numbers With Commas
Jul 23, 2005I am pulling several numbers from a SQL table, adding them and doingvarious calculaitons. The numbers do not display a comma to separatethousands. What is a way to format this?
View 5 Replies View RelatedFormating Numbers In SSIS
Oct 13, 2006I have a Amount field which is declared as Decimal. the data for this will be somethin like this 0.152
output need it to be -
00000.1520
How can I do this?
FOrmating SQL DateType To String Format
Jun 11, 2008I have the information below
PRINT REPLACE(REPLACE(CONVERT(varchar(50), GetDate()), ' ', ''), ':', '')
What I would Like to do is run the query above and get date of this type '2003-05-08 12:35:29.998' returned as '20030508123529998'
I know GetDate does not even come up with date in the first format so I need to know which way to get it in the first format so that I can run the query and get the second format string returned to me. Thanks in Advance
Format Numbers With Commas
Jul 4, 2007Hello Nice simple T-SQL question that will no doubt prove totally uncontroversial :) Anyone know the easiest way to get from:123456789to123,456,789in T-SQL? Currently I cast my int as money, use convert to varchar with style 1 and then lop off the .00. Is there an easier way? Two things before you get all upset and start squealing about front ends:1) These are admin functions that I am running from SSMS. I don't want to go to the bother of creating an application when SSMS is perfectly adequate for what I need (namely to-a-large-degree unformatted result sets).2) I'm not too fussed about it just curious. Ta & tra la!
View 2 Replies View RelatedExcel Numbers Format
Feb 22, 2007Hi all i'm having an issues when i trying to do a report:
i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.
I would really appriciate your Help
Thanks
Getting Series Of Numbers To Format For Dates
Jul 12, 2007if
(datalength
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD])
) = 1)
then
(0 ||
cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
else
(cast_varchar
([Costpoint].[Profit_Calc_Consolidation].[PD]))
Goal:
To be able to tell the Period number ([PD]) to change from an integer to a var_char so that it can be concatenated with the Fiscal Year. I.e. Fiscal Year || Period number to produce the following results.
2006 01
2006 02
2006 03
Etc……
Status of Problem:
The issue is that I can only get it done to the point to where the Fiscal Year || Period number yields the following results:
20061
200610
200611
200612
20062
Etc….
This is as issue since I am trying to get all the information from the past to a certain year and period. Using a filter that says
Fiscal Year + Period Number <= ?Prompt_Fiscal_Year? + ?Prompt_Period_Number?
So if I enter Year: 2007 Period 3
I should get:
2006 01
2006 02
2006 03
Etc…….
2006 11
2006 12
2007 01
2007 02
2007 03
Can you please let me know if my thinking is off or if my SQL is wrong, Thank you for your assistance in this matter.
Cannot Format Numbers To CSV Destination File
Mar 3, 2006Hi,
I would like to know if there is any trick to convert numbers to csv files. I don't have any control to format the numbers.
Source: Sql server
for example:
select cast( 1 as float)/ cast(201 as float)
Destination:CSV file
the output of the file is 4,9751243E-3
i don't want to have scientific notation on the number. I want the result to be 0,00497512437810945. how can i do it?
I tried to change the type of destination columns to String or numeric but nothing works. what is the influence of changing data type in the destination columns e the output to files?
I tried using convert but with the same results.
Can it be because of local settings or something else?
thanks
prb222
Format Numbers On Chart Y Axis
Jan 2, 2007Hi!
How can I format number on chart's y axis? I wanto to show 1.000 instead of 1000.
Thank you!
FOR XML Issue With Numbers Coming Across In Scientific Format
Dec 5, 2006Program Descritpion: Our product allows our customers to enter their product catalogs into our ASP.NET pages and we save the data using SQL Server 2005. Customer use GridViews to edit their data and teh data is broken into manageable groupings (called Field Groups) stored as meta-data in SQL Server Tables. Then when a user wants to edit a set of data they choose the Field Group and we dynamically generate a data source and a gridview, bind them together, and our users update their data. A bit more complicated but that's teh gist.
Another thing they can do, and where our problem occurs, is they can print reports or do exports into excel using this data. Since there are so many fields, they use the Field Groups to select which fields they want included so everything needs to be built dynamcially, at runtime, using the Field Group meta-data. Essentially, I want my grid to have these 10 fields.
Problem:For the export we generate an XML data source using FOR XML in SQL Sprocs. The xml is pulled into a xmldatadocument, trasnformed with an xsl file into an Excel XML Spreadsheet. Our problem is that FOR XML is generating our XML real numbers in scientific format. The xsl "format-number" function does not recognize scientific format and returns NaN (not a number) instead of the value in my spreadsheet. If I leave it blank I get the scientific number but Excel doesn't format it correctly, the cell has an error tag that wants me to choose Number stored as text or convert to a number. I need it to show up on teh Excel form already formatted without that message.
I can't change the DataType of the field in SQL, too many other things depend on it and it needs to be a real. I can't use CONVERT(decimal,fieldName) in SQL because the SQL string is dynmically generated using Dynamic SQL and most of the fields are not real. When we build the Field List we just have field names, we can't check anything to add CONVERT functions to only real fields.
Is there any way to force the XML output to not be scientific for the entire document? Or another function in XSL I'm unaware of (pretty new to xsl)? Or perhaps something I can do in the XML Spreadsheet tags to force the conversion?
Fixing Phone Numbers To Correct Format
Aug 14, 2014 I am trying to find all the records in our database that have the incorrect phone number format and fix them to the correct format.
CREATE TABLE MDR (
SiteName nvarchar(255),
BusinessEmailAddress nvarchar(255),
FirstName nvarchar(255),
LastName nvarchar(255),
JobTitle nvarchar(255),
PersonBusinessPhoneNumber nvarchar(255),
SiteBusinessPhoneNumber nvarchar(255))
[code]....
Format Strings For Fractional Numbers And Currency
Nov 17, 2005/*
Format strings for fractional Numbers and Currency values
Return Values: VARCHAR
Parameters: @n Specifies numeric expression to format.
@sFormat: Specifies one or more format codes that determine how the expression is formatted.
The following table lists the available format codes.
9.000000|00 |09
12.100000|### |12
12345.120000|### ### ###.000|12 345.120
12345.120000|### ### ###.###|12 345.12
12345.120000|$ ### ### ###.000|$ 12 345.120
12345.120000|### ### ###.### $|12 345.12 $
12345.120000|$ ###,###,###.000|$ 12,345.120
12345.120000|### ### ###.000|12 345.120
12345.120000| |12345
1.120000|### ### ###.000|1.120
12.120000|### ### ###.000|12.120
123.120000|### ### ###.000|123.120
1234.120000|### ### ###.000|1 234.120
12345.120000|### ### ###.000|12 345.120
123456.120000|### ### ###.000|123 456.120
1234567.120000|### ### ###.000|1 234 567.120
12345678.120000|### ### ###.000|12 345 678.120
123456789.120000|### ### ###.000|123 456 789.120
1234567890.120000|### ### ###.000|1234 567 890.120
12345678901.120000|### ### ###.000|12345 678 901.120
123456789012.120000|### ### ###.000|123456 789 012.120
*/
CREATE FUNCTION xNumberFormat(@n NUMERIC(38, 4), @sFormat VARCHAR(255))
RETURNS VARCHAR(255) AS
BEGIN
DECLARE @sRet VARCHAR(255), @i TINYINT, @j INT, @nDec TINYINT, @sNumber VARCHAR(255), @cF CHAR(1), @cR CHAR(1), @sE VARCHAR(255), @sX VARCHAR(255)
SELECT @sE = '', @i = LEN(@sFormat)
WHILE @i > 0 AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sE = SUBSTRING(@sFormat, @i, 1) + @sE, @i = @i -1
SELECT @sFormat = LEFT(@sFormat, @i), @sX = '', @i = 1
WHILE @i < LEN(@sFormat) AND SUBSTRING(@sFormat, @i, 1) NOT IN ('#', '0') SELECT @sX = @sX + SUBSTRING(@sFormat, @i, 1), @i = @i +1
SELECT @sFormat = RIGHT(@sFormat, LEN(@sFormat) - @i + 1)
IF @n = 0 AND CHARINDEX('0', @sFormat) = 0 AND @sE = '' AND @sX = '' RETURN ''
SET @nDec = CHARINDEX('.', @sFormat)
IF @nDec > 0 SET @nDec = LEN(@sFormat) - @nDec
SET @sNumber = RTRIM(LTRIM(STR(@n, 255, @nDec)))
IF @nDec > 0 SET @nDec = @nDec + 1
SET @sRet = RIGHT(@sNumber, @nDec)
IF @nDec > 0
BEGIN
SET @i = 1
WHILE RIGHT(@sRet, 1) = '0' AND SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1) = '#' SELECT @sRet = LEFT(@sRet, LEN(@sRet) - 1), @i = @i + 1
IF @sRet = '.' SET @sRet = ''
END
SELECT @i = @nDec + 1, @j = @nDec + 1
WHILE @i <= LEN(@sFormat) AND @j <= LEN(@sNumber)
BEGIN
SELECT @cF = SUBSTRING(@sFormat, LEN(@sFormat) - @i + 1, 1), @cR = SUBSTRING(@sNumber, LEN(@sNumber) - @j + 1, 1)
IF @cF NOT IN ('#', '0')
IF @j = LEN(@sNumber) AND @n < 0 SET @i = @i + 1 ELSE SELECT @sRet = @cF + @sRet, @i = @i + 1
ELSE
SELECT @sRet = @cR + @sRet, @i = @i + 1, @j = @j +1
END
IF @j <= LEN(@sNumber) SET @sRet = LEFT(@sNumber, LEN(@sNumber) - @j + 1) + @sRet
WHILE @i <= LEN(@sFormat) AND SUBSTRING(@sFormat, @i - @j + 1 , 1) = '0' SELECT @sRet = '0' + @sRet, @i = @i + 1
RETURN @sX + @sRet + @sE
END
Can't Convert Negative Numbers From String Format
Oct 11, 2007Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-
The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.
Any ideas?
Thank you for your help!
cdun2
Analysis :: Format Numbers In Drill-through Action?
Jun 15, 2015I have table with 3 columns: record_id (int), car (varchar(50)), amount (decimal(18,4)).
I created dimension for drillthrough action. Also I set Amount's column FormatString to '##0.00' but when I use Drillthough column Amount values is shown like:
.0000
15.7900
18.4100
Is there any possibility to change that values would be shown as follow:
0,00
15,7900
18,4100
Unable To Format Excel Destination Column For Numbers
Oct 31, 2006Hi,
I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.
Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.
Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?
Thanks
Numbers(format=N2) Showing As Strings In Matrix-&&>excel Export
Jun 7, 2007Anyone know why my xls does not have number formats when exporting from a report with a matrix?
I keep having to do 'paste special multiply by 1' on the whole thing.
Ridiculous!
Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers
Feb 1, 2007I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
Query Analyzer Shows Negative Numbers As Positive Numbers
Jul 20, 2005Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies View RelatedI Need To Update A Table With Random Numbers Or Sequential Numbers
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
Generate List Of All Numbers (numbers Not In Use)
Feb 21, 2007I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error
Mar 27, 2007I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
DateTime Formating.
Oct 5, 2006I am trying to query a calendar table that has the [start] datetime of an event and then a [title] of the event. I am trying to display them as;* 8:00 AM Get to work* Independance daysome of the events have a start time and some only have a date. the ones that do not have a start time have a start time of 12:00:00 How do I query the two fields and only show the start time & title of the events that have a start time and just the title for the ones that do not have a start time.. I've tried this...select Event = (CONVERT(CHAR(8),start,8) +' ' + title) from Calendar where CONVERT(CHAR(8),start,8) <> '12:00:00'It does display the start and event title but only for the ones that have a start time. And how do I get the 8:00 AM format for the time? Thanks for any help.Mark
View 3 Replies View RelatedFORMATING THE SQL OUTPUT
May 17, 2001Hi!
I have couple of questions. Anybody can help me.
1. How can I format the output in T-SQL coming like as one can do in SQL*PLUS (ORACLE)?
2. I decalerd a Cursor and using that It is generating the output. I jut want to dispaly only 100 rows, its mean that I want to control the output.
Please give your feedback
Formating Date
Aug 6, 2002How do I get the following format using SQL server ( SQL QUERY ??)
yymmddhh.
In Oracle I can do simple...
Select to_char(datecolumn, 'YYMMDDHH') from tablename;
RESULT > 02080603
-- YEAR, MONTH, DATE, HOUR.
What is that I need to do to get the same result on sql server.?
tks
Text Formating
Nov 9, 2006i have this:
\FILInternalBuisnessArea*.*
\FILInternalBuisnessArea
\FILInternalBuisnessArea*.qvw
\FILInternalBuisnessArea empfileDB.mdb
\FILInternalPublic Area*.*
\FILInternalPublic Area
\FILInternalPublic Area*.xls
\FILInternalPublic Areaacro*.doc
\FILInternalPublic Areaeconomi*.xls
\FILInternalPublic AreaeconomiGroup01FileToSave*.xls
\FILInternalPublic AreaeconomiGroup01Unit01FileToSave*.xls
And i want to strip away whatever is after the last '' so the output should look like this
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalBuisnessArea
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Area
\FILInternalPublic Areaeconomi
\FILInternalPublic AreaeconomiGroup01
\FILInternalPublic AreaeconomiGroup01unit01
datatype = varchar (500)
thx alot in advance
//Mr
Formating The Record
Apr 7, 2008Hi all,
i am using sql server 2005.
let me explain the scenario.
I have to built a report where I have to find out no of call made by an executive to a perticular customer during a year
my table record look like
empid custid product date and other field
em111 iimran aaaa 01-04-2008 ........
em111 iimran bbbb 01-04-2008 ........
em111 iimran cccc 01-04-2008 ........
em111 xxxxxx aaaa 01-04-2008 ........
em111 xxxxxx 2222 01-04-2008 ........
em111 iimran aaaa 02-04-2008 ........
em111 iimran bbbb 02-04-2008 ........
em111 iimran cccc 02-04-2008 ........
em111 xxxxxx aaaa 02-04-2008 ........
em111 xxxxxx 2222 02-04-2008 ........
.
problem is it has to implement in this manner
Srno custname jan feb mar apr may june july aug sep oct nov dec
1 iimran 0 4 0 5 6 7 0 3 5 7 3 1
please suggest some idea.
Please Help
Formating A String In Sql
May 5, 2008Ok so here is an explanation of what i need. I have a table that has emails in it. I need to get those emails to show up as verticle line of names. these will then be sent out via email so i have the following line of code to put them into a string:
select @emails1 = isnull (@emails1 + '; ','' + char(13))+ char(13) + email from emaillist
I have tried putting Char(13) in multiple areas trying to get line break. but the email sends the list in a straight line.
can anyone help me. also if you need more info let me know i will give it to you.
example of desired output
xxxx@dgfasg.com
sfgasg@asgahi.com
saghna@asogh.com
Formating Problem
Dec 18, 2007hi all
can anybody tell me how to get double underline for the text box value in reporting services
am using sql server reporting services 2000
Condtional Formating
Oct 26, 2007
Hi all,
This expression gives me an error anyone know why?
=IIF(Fields!M.Value = 0,"Average",(MonthName(Fields!M.Value)))
Thanks
Drill Down Formating
May 22, 2006Does anyone know what code I'd use in an expression to conditionally format based on if the drill down is expanded or collapsed?
Thanks.
in an
in-an
inane
Inna
Ian
Edit...
Revert to "inan"conditionally
conditional
conditionals
conditionality
Edit...
Revert to "conditionaly"
Formating Datetime
Mar 14, 2008I have a decimal time field that I would like to convert to "hh:mms"
If I try to use =cdate(Fields!Avg_Visit_Duration.Value) I get #Error in my result. The error message is:
Conversion from type 'Double' to type 'Date' is not valid.
0.0044612654 should be shown as 00:06:25. I can do this very easy in SSAS or Excel... SSRS is giving me fits.
edit: made question clearer. nlm
Date Formating - Military
Apr 13, 2007Hello all,
I am trying to import an excel spreadsheet that has a specific date column. The column is saved as military time. For example, one of the records is:
04-12-2007 09:30
But, when i import the data into sql 2005, the above date is displayed as:
2007-04-12 09:30
I need it to display like the same way that it's being displayed in the excel spreadsheet.
My field in sql is nvarchar(100) and it still doesn't work.
Any ideas?Thanks in advance.
Formating The Output Of Mails
Nov 16, 2001Hello,
I'm using xp_sendmail in the sp and in @query I do select * from table1.
The result also page,s
The formate is not at all readable and even if @attach_results = 'TRUE'
In the text file its not formatted as it sends mail to my manager I want it to be formatted and looks good. Can anyone help me or Is there any way to get the results in readable format.
Thanks in Advance for the help
Rani