Converting decimal numbers to Words

Hi there all the Gurus,
I am trying to mave a convert a decimal number ie. 1230.30 to words in my report and i am getting result with the 30/100 at the back of the words.
Is there any way i can get a result of :
'ONE THOUSAND TWO HUNDRED THIRTY AND THIRTY'Well, I guess you could split the number into two parts (at the decimal point) and convert each separately.|||UpperCase(TOWORDS(integer({INVOICE.AMOUNT}))) + Uppercase(TOWORDS(fraction({INVOICE.AMOUNT}))) + 'CENTS ONLY'

i have tried this formula but it keeps prompting error "Missing '('

Please advice|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');|||Cool! Many thanks for the solution.|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi, if I want to convert a Sum of Amount into English Words? How to do this?|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)|||Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)

Yap, I have already tried in this way, which replace the 12345.67 into sum(@.amount), yet it doesn't work.

local stringvar sNumber := cstr(sum({@.Amount}));
local numbervar whole := truncate(sum({@.Amount}));
local numbervar pos := instrrev(sNumber, '.');

local numbervar decimal := 0;
if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Ringgit and ' + ToWords(decimal, 0) + ' Sen Sahaja');|||What error do you get? Presumably 'This field cannot be summarised' when you run the report as you can't sum a formula {@.amount}.
Try summing something that can be summed like the underlying database value, or maybe {@.amount} is already your summed value?sqlsql

Converting a string of binary numbers to a binary datatype

I have a varchar field which holds numeric (binary) data e.g. '00101111' and I want to convert this to a binary data type with the value e.g. 0x00101111

But when I try the following SQL:

select top 5 flag2,convert(binary,flag2) flag2_as_binary from my_table

I get:

flag2 flag2_as_binary
--- -------------------
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000000 0x303030303030303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000
00000100 0x303030303031303000000000000000000000000000000000 000000000000

(5 row(s) affected)

I want some SQL that will return the following (with flag2_as_binary as a real binary datatype):

flag2 flag2_as_binary
--- -------------------
00000000 0x00000000
00000000 0x00000000
00000000 0x00000000
00000100 0x00000100
00000100 0x00000100

(5 row(s) affected)

Thanks.The sql binary datatype is actually displayed hexidecimal base 16 usinge characters 0-9 and A-F, not base 2.

Converting 7 (int) to 07 (varchar)

Hi,

I'm converting a query from Access to SQL Server.

In this query I select from a column that contains numbers, the result I want is a varchar that is always 2 chars wide..

Ie:
7 should be selected as '07'
12 should be selected as '12'

In the Access-query it's rather nicely done with:
Format(Str(mycolumn),"00")

I could not find a way to make CONVERT do the same job... but I found that:
LEFT('00',2-LEN(CAST(mycolumn as varchar)))+CAST(mycolumn as varchar)
will do the job.

But it feels like it could be done nicer.. any suggestions?Im Pretty new to this myself but how about

RIGHT('00' + CAST(MyColumn as Varchar(2)),2)

Dave|||Thanks, I was too left oriented in my thinking :-)

Convert to Number IF it is a number

Pardon my newbieness:
I have a column that could contain numbers or text (not my idea). All
of the values are returned as text. I want to return the value as a
number if it is a number, otherwise (of course) leave it as text. What
can I do?Return the value to where?
A single column in a resultset has exactly one datatype.
Can't your receiving application or front end code figure out what kind of
data it is?
Why are you storing numbers and strings in the same column?
A
"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>|||> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
Use the ISNUMERIC function. Check the syntax in Books OnLine.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com|||"Tod" <todtown@.swbell.net> wrote in message
news:1136318800.755299.143920@.f14g2000cwb.googlegroups.com...
> Pardon my newbieness:
> I have a column that could contain numbers or text (not my idea). All
> of the values are returned as text. I want to return the value as a
> number if it is a number, otherwise (of course) leave it as text. What
> can I do?
>
A column only has a single datatype so I'm going to assume you require a
result set with two columns: one VARCHAR, one INTEGER. I will also assume
that when you say "text" you really mean "VARCHAR". Doing this using the
TEXT datatype would be a bit more tricky. That's too many assumptions but if
you don't post a more precise spec then assumptions and guesses are what you
will get. You can help us to help you better by taking the advice in the
following article:
http://www.aspfaq.com/etiquette.asp?id=5006
Here's my example:
CREATE TABLE tbl (x VARCHAR(10) PRIMARY KEY) ;
INSERT INTO tbl (x)
SELECT '123' UNION ALL
SELECT '99A' UNION ALL
SELECT 'ABC' ;
SELECT x, CAST(num AS INTEGER) AS num
FROM
(SELECT x,
CASE WHEN x NOT LIKE '%[^0-9]%' THEN x END AS num
FROM tbl) AS T ;
Result:
x num
-- --
123 123
99A NULL
ABC NULL
(3 row(s) affected)
Hope this helps.
David Portas
SQL Server MVP
--|||"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:%23R%23NQQKEGHA.1264@.TK2MSFTNGP09.phx.gbl...
> Use the ISNUMERIC function. Check the syntax in Books OnLine.
> --
> Dejan Sarka, SQL Server MVP
> Mentor
> www.SolidQualityLearning.com
>
Before using ISNUMERIC Tod should be aware of its limitations. Read:
http://www.aspfaq.com/show.asp?id=2390
David Portas
SQL Server MVP
--|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.|||Thanx for all of the replies. It gives me a better idea about what I
need to be doing.

Convert to month year

I have numbers like 16, 30. Now I have to convert them to year and
months like 16 would be 1 year 4 months. Thanks.declare @.num int
set @.num = 16
select @.num/12 as years, @.num % 12 as months
"Dipak" <dipakpaudel@.gmail.com> wrote in message
news:1128696439.770616.185290@.f14g2000cwb.googlegroups.com...
>I have numbers like 16, 30. Now I have to convert them to year and
> months like 16 would be 1 year 4 months. Thanks.
>

Convert SSN (varchar) to Number

Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.Is casting really necessary? Could you try:
select
max (SSN)
from
MyTable
where
SSN between '001330000' and '001379999'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.|||Take a look at this
select RIGHT('000000000' + CONVERT(VARCHAR(9),12345),9)
change 12345 to your output
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ILCSP@.NETZERO.NET wrote:
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||What does Homeland Security think about you creating 'fake SSNs'?
g,d,r
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
>
Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA
will be looking for you... :-)
Oh crap, now me too...|||What if you create a 'fake' SSN for someone and then later on someone
signs up with that SSN, then what happens? Hint: Big Headache since
you have to assign a new 'fake' SSN to the old person and changes this
probably all over the place
Better to use XXXX00001 or some other Format like FAKE00001 etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> > Hello, I'm trying to get the Max number from a range of Social Security
> > numbers in my SQL 2000 Customer table. The SSNs as stored as
> > varchar(9).
> >
> > I'm supposed to get the SSN with the 'highest' numbers from 001330000
> > to 001379999 range.
> >
> > I know that number should be 001340062, but I never got it. I've tried
> > to convert it and cast it, but so far I haven't been able to do so.
> > When I converted it, I got only 1340062 (it was missing the first 2
> > zeroes).
> >
> > We're creating fakes SSNs for people who don't have one and I'm
> > supposed to know which one is the max we've created so far and then add
> > 1 to it and so on and so on. This worked in Access, but when we
> > upgraded to SQL it didn't work anymore.
> >
> > Any help would be appreciated it.
> >
> > Thanks.
> >|||Hello all, I used Tom's way and it works great. Thanks for replying.
I think I should have not used the phrase fake SSNs, but I guess I just
wanted to get some attention.
:-)
Tom Moreau wrote:
> Is casting really necessary? Could you try:
> select
> max (SSN)
> from
> MyTable
> where
> SSN between '001330000' and '001379999'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
I would think they are already contacting
CoreComm - Voyager, Inc
East Lansing, Michigan|||You're guarenteed to be high on the DHS/NSA watch list!
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151438702.180395.194640@.u72g2000cwu.googlegroups.com...
> Hello all, I used Tom's way and it works great. Thanks for replying.
> I think I should have not used the phrase fake SSNs, but I guess I just
> wanted to get some attention.
> :-)
>
> Tom Moreau wrote:
>> Is casting really necessary? Could you try:
>> select
>> max (SSN)
>> from
>> MyTable
>> where
>> SSN between '001330000' and '001379999'
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> <ILCSP@.NETZERO.NET> wrote in message
>> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
>> Hello, I'm trying to get the Max number from a range of Social Security
>> numbers in my SQL 2000 Customer table. The SSNs as stored as
>> varchar(9).
>> I'm supposed to get the SSN with the 'highest' numbers from 001330000
>> to 001379999 range.
>> I know that number should be 001340062, but I never got it. I've tried
>> to convert it and cast it, but so far I haven't been able to do so.
>> When I converted it, I got only 1340062 (it was missing the first 2
>> zeroes).
>> We're creating fakes SSNs for people who don't have one and I'm
>> supposed to know which one is the max we've created so far and then add
>> 1 to it and so on and so on. This worked in Access, but when we
>> upgraded to SQL it didn't work anymore.
>> Any help would be appreciated it.
>> Thanks.
>|||Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
assign them in a range that is currently in use by a state. That's just
asking for trouble.
According to the SSA, any valid number beginning with "000" will never be a
valid SSN. Here are some links to help with the business rules:
http://www.socialsecurity.gov/employer/highgroup.txt
http://www.socialsecurity.gov/employer/stateweb.htm
Given the three numbers you've passed in, the SELECT below will grab the
MAX(). It sounds like you want to add one and left pad it with zeroes.
Here's a sample:
CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @.iSSN INT
SELECT @.iSSN = CAST(@.ssn AS INT) + 1
DECLARE @.newSSN VARCHAR(9)
SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
RETURN @.newSSN
END
GO
CREATE TABLE #temp(ssn VARCHAR(9))
INSERT INTO #temp(ssn)
SELECT '001330000'
UNION SELECT '001379999'
UNION SELECT '001340062'
SELECT dbo.AddOneAndPadSSN(MAX(ssn))
FROM #temp
DROP TABLE #temp
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Nobody here but us wannabe fashion clothes reps... :)
"Michael Kujawa" <nof at kujawas dot net> wrote in message
news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
>> --
>> Arnie Rowland, YACE*
>
> I would think they are already contacting
> CoreComm - Voyager, Inc
> East Lansing, Michigan
>|||One would think they don't have enough people to cover the newsgroups, what
with trying to keep up with all of our phone calls :)
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uTsVwOimGHA.856@.TK2MSFTNGP03.phx.gbl...
> Arnie Rowland wrote:
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
> Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA will
> be looking for you... :-)
> Oh crap, now me too...|||SSN are not as aggressively validated in the States the way they are in
Canada. With SSI numbers (the Canadian equivalent of SSNs) your number is
validated along with your birthdate before your first pay check is cut.
Providing an employer with a fake SSI number will only generate a request
for validation from Revenue Canada if the name and birthdate so not match
and the SSI number is not already in the system.
In America you have to prove your authorization to work in the States which
may or may not include you displaying a Social Security card to your
employer. If you provide them with a bogus number and they do not request to
see the card at some point in time the Social Security Administration may
make a request of your employer for validation of the number you supplied at
some point down the road - normally a year down the road.
Social Security Numbers have no checksum on them the way credit card numbers
do. The first three digits normally indicate where it was issued, and
immigrants typically receive their numbers from a different bank. Locales
may exhaust their number allotments before other locales and they may pull
from the same number pool as a less populous area.
Please refer to this document for more information.
http://en.wikipedia.org/wiki/Social_security_number#Valid_SSNs
I had a friend who had a completely bogus SSN for years with no problems.
Most frequently you run into problems when you and someone else uses the
same number, but with different names for it.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23GbmMmimGHA.5052@.TK2MSFTNGP04.phx.gbl...
> Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
> advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
> assign them in a range that is currently in use by a state. That's just
> asking for trouble.
> According to the SSA, any valid number beginning with "000" will never be
> a valid SSN. Here are some links to help with the business rules:
> http://www.socialsecurity.gov/employer/highgroup.txt
> http://www.socialsecurity.gov/employer/stateweb.htm
> Given the three numbers you've passed in, the SELECT below will grab the
> MAX(). It sounds like you want to add one and left pad it with zeroes.
> Here's a sample:
> CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
> RETURNS VARCHAR(9)
> AS
> BEGIN
> DECLARE @.iSSN INT
> SELECT @.iSSN = CAST(@.ssn AS INT) + 1
> DECLARE @.newSSN VARCHAR(9)
> SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
> RETURN @.newSSN
> END
> GO
> CREATE TABLE #temp(ssn VARCHAR(9))
> INSERT INTO #temp(ssn)
> SELECT '001330000'
> UNION SELECT '001379999'
> UNION SELECT '001340062'
> SELECT dbo.AddOneAndPadSSN(MAX(ssn))
> FROM #temp
> DROP TABLE #temp
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
>> Hello, I'm trying to get the Max number from a range of Social Security
>> numbers in my SQL 2000 Customer table. The SSNs as stored as
>> varchar(9).
>> I'm supposed to get the SSN with the 'highest' numbers from 001330000
>> to 001379999 range.
>> I know that number should be 001340062, but I never got it. I've tried
>> to convert it and cast it, but so far I haven't been able to do so.
>> When I converted it, I got only 1340062 (it was missing the first 2
>> zeroes).
>> We're creating fakes SSNs for people who don't have one and I'm
>> supposed to know which one is the max we've created so far and then add
>> 1 to it and so on and so on. This worked in Access, but when we
>> upgraded to SQL it didn't work anymore.
>> Any help would be appreciated it.
>> Thanks.
>|||Hey!! Bugger off! I wanted that job.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Mike C#" <xyz@.xyz.com> wrote in message
news:O%238n7mimGHA.1404@.TK2MSFTNGP05.phx.gbl...
> Nobody here but us wannabe fashion clothes reps... :)
> "Michael Kujawa" <nof at kujawas dot net> wrote in message
> news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>>
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
>> --
>> Arnie Rowland, YACE*
>>
>> I would think they are already contacting
>> CoreComm - Voyager, Inc
>> East Lansing, Michigan
>>
>|||SSN can be aggressively validated. Where I used to work we actually ran
validations, and discovered some fascinating things. Like several people in
a 6-block radius of Los Angeles with the same SSN :) Employers here are
supposed to look at, and make copies of, your SSN card when they hire you
(I-9 form). Of course no one from the government ever checks up on their
I-9 forms, so employers don't really go out of their way to validate the
information given to them...
Anyway, some info about SSNs:
-The first three digits are normally assigned based on the state or
territory/protectorate belonging to the ZIP code you mailed your application
in from. Some are assigned to U.S. citizens abroad (U.S. Embassies,
Enumeration at Entry) and some were assigned by the Railroad Board back in
the day.
-Numbers beginning with 000, 666 or 900 - 999 will not be assigned by the
SSA (they're used for internal government agencies). Numbers with 00 in the
middle or 0000 at the end will not be issued.
-There are some groups of Americans who refuse to get SSN's. The Amish are
an example of these groups.
-The first SSN ever assigned was to John D. Sweeney, Jr., of New Rochelle,
NY.
-The lowest SSN ever assigned was 001-01-0001 to former N.H. Governor and
Social Security Board Chairman John G. Winant. He turned it down. Then it
was offered to John Campbell of the Federal Bureau of Old Age Benefits. He
also turned it down. Finally it was assigned to the first applicant from
New Hampshire: Grace D. Owen of Concord.
Don't ask why I know all this crap about SSN's :) My old job required a lot
of investigation into it for validation purposes. In fact, a while back I
posted a basic SSN validation web service to Code Project that performs all
of the basic validations, including high group check, group validation and a
check against the invalidated SSN list (those used in advertising and
invalidate by the SSA): http://www.codeproject.com/aspnet/ssnvalidator.asp.

convert sql smalldatetime column into numbers

HI all;

I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.

Any help would be highly appreciated, thanks all.select convert(varchar,getdate(),112) ??

Convert question

Hi,
I am importing data form a text file and I have a problem with a column
format
In the file, the numbers are are stored with this format: -54.565,49
Now I have to change the format to -54565.49 to store this value into a
decimal using DTS
Could you help me ?
ThanksDid you transpose the ',' and the '.' ? Is the original format really
-54.565,49 or -54,565.49?
"Javier" wrote:

> Hi,
> I am importing data form a text file and I have a problem with a colum
n
> format
> In the file, the numbers are are stored with this format: -54.565,49
> Now I have to change the format to -54565.49 to store this value into a
> decimal using DTS
> Could you help me ?
> Thanks
>
>|||Yes, the format is -54.565,49
The values are stored in a text file and I can not change it
Thanks
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:7853108E-79F4-4C23-805A-EEE5EDE2781C@.microsoft.com...
> Did you transpose the ',' and the '.' ? Is the original format really
> -54.565,49 or -54,565.49?
>
> "Javier" wrote:
>|||Hi
It may work if you change the regional settings, although you may want to
make the solution non-region specific! One way would to use and activeX
transformation and treat the field as a string. You can then use the replace
function in your script to remove the thousand delimiter and replace the
commas with a decimal point.
John
"Javier" <jvillegas@.elsitio.net> wrote in message
news:erY%23rClCGHA.2644@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am importing data form a text file and I have a problem with a column
> format
> In the file, the numbers are are stored with this format: -54.565,49
> Now I have to change the format to -54565.49 to store this value into a
> decimal using DTS
> Could you help me ?
> Thanks
>|||Using T-SQL, you could use a statement like...
Select select replace(replace(<Column Name>,'.',''),',','.')
The inside Replace function changes the decimal point to nothing at
all. The outside function changes the comma to a decimal point. The
order of operations is very important here.

convert positive and negative numbers

I have a sales report where sales dollars are negative and credits and returns show as positive numbers. I would like to change the negative numbers to positive numbers and the positive numbers to negative numbers.
Thank you for any help.Originally posted by DeannaUTI
I have a sales report where sales dollars are negative and credits and returns show as positive numbers. I would like to change the negative numbers to positive numbers and the positive numbers to negative numbers.

Thank you for any help.

Hi,

1. Convert negative value to positive

abs(myvar)

2. Convert positive value to negative

myvar - (myvar * 2)

yours friendly,
K.Babu

Convert numbers to Month Name

I have a stupid field in my database that doesn't hold a date but I have to use it to determine the month in my Chart in SSRS 2005. So for example, check this out:

http://www.webfound.net/chart_months.jpg

Ok, so how can I change those numbers to the Month Name? I tried MonthName() around my field in the expression builder, but that's only for a datetime field.

Write a UDF with input as int month and return it as varchar month. Use 13 CASE statements (12 for 12 months and 1 for error)|||

How about something like convert(datetime,'2006' + <fieldname> + '01') within your SQL statement?

for padding 0's

select convert(datetime,'2006' + fieldname + '01')
from
( select case <fieldname> when > 9 then <fieldname> else '0' + convert(char(1),<fieldname>) end fieldname ) derivedtable1

|||

The function MonthName, in SRSS2005, accepts an Integer parameter, so I'm not sure I understood the question.

|||hmm, then I wonder why that didn't work...MonthName(fieldname)|||

Paulo X , look at the link to my chart above. Those are integer values coming in from my dataset on a field in our DB table called systemmonth. Don't ask me why they did it that way but I need to take those values and convert them to Month Names....hopeing to do this either through SQL or preferably using a function in Reporting Server 2005 as you stated. I tried wrapping the systemmonth like this but it didn't have any affect:

MonthName(myfieldname)

|||

this is what I'm talking about, I put this in as a category group field and created this expression behind it:

=MonthName(Fields!SystemMonth.Value)

|||

What happens when you use MonthName? Is there any error message?
You say that you have used the MonthName function into the group expression. You must used it also in the Label expression! In fact, you may use MonthName only in the Label, you don't need to use it to group data.

Regards

|||

Hi,

Just try

=Monthname(1) or

=MonthName("1")

Is it working. then assign your field values. the second statement also works.

Amarnath

convert numbers in SQL

how can i format numbers in SQL
Eg. i have number such as 1250.000002341 in a table [money].[Paid]
how can i fornat that number to 125,00?With the number stored in "money.paid", useCAST(money.paid AS DECIMAL(10,2))|||formatting is usually a function of your user interface. So its rare to come accross a requirement like this in SQL. Where are you using this information - in a report, in a screen, in a text file?

Convert Numbers

Hi there, I'm using a SQL 2000 and I don't know how to convert (or calculate)some data as described below:

Examples
746585 means 7 hours, 46 minutes and 58.5 seconds
038335 means 38 minutes and 33.5 seconds
005215 means 5 minutes and 21.5 seconds
000455 means 45.5 seconds
000070 means 7 seconds
000065 means 6.5 seconds

How do I convert:
746585 in 466.975 minutes
038335 in 38.558 minutes
005215 in 5.358 minutes
000455 in 0.758 minutes
000070 in 0.166 minutes
000065 in 0.1083 minutes

Could anyone help me?

Thanks in advance,

AndreOkay, you have a string with numbers of the following meaning:
hmmssc

To convert it into a datetime value:

declare @.s as char(6)
set @.s='746585'

select convert(datetime,'0'+substring(@.s,1,1)+':'+
substring(@.s,2,2)+':'+substring(@.s,4,2)+'.'+
substring(@.s,6,1), 14)
go

To compute the minutes:

select cast(substring(@.s,1,1) as int)*60+
cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Cheers! :D|||Thank you for your reply DoktorBlue you hit almost there!!! Sorry, I just started with SQL Server...

I tryed that string:
declare @.s as char(6)
set @.s='746585'

select convert(datetime,'0'+substring(@.s,1,1)+':'+
substring(@.s,2,2)+':'+substring(@.s,4,2)+'.'+
substring(@.s,6,1), 14)
go

select cast(substring(@.s,1,1) as int)*60+
cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Then I got the result:
1900-01-01 07:46:58.500

The time 07:46:58.500 is very correct but it should mean a period of time, not a date.

There is a file available at http://www.aga.cc/download/sample.txt (the original one has over than 200 thousand records), so I need to calculate the total of each access#'s time and cost, something like this:

select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#.

Thank you again for your cooperation.

Andre Mori|||Originally posted by mori
select access#, sum (time) as TIME, sum (cost) AS PRICE, sum (PRICE) / sum (TIME) as MONEY from sample group by access#.


Hi Andre,

You want to have sum(time). In minutes? Use the second transformation.

You want to display your time as a string? Consider to write something like

select substring(@.s,1,1) + ' hours, ' +
substring(@.s,2,2) + ' minutes, ' + substring(@.s,4,2) +
'.' + substring(@.s,6,1) + ' seconds'|||Great DoktorBlue, 100530 converted into 60.883333333, worked fine!!! Thank you very much.

I have another awful question, how do I set @.s='the_collumn_i_want_to_convert'?

Once again, thanks.|||I used a variable to show the principle. You have to replace @.s by your field name.|||Hi DoktorBlue, please don't get angry OK?

I tryed:

declare @.time as char(6)
set @.Time = Time

select cast(substring(@.Time,1,1) as int)*60+cast(substring(@.Time,2,2) as int)+ cast(substring(@.Time,4,3) as dec(10,5))/600

Results:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'Time'.

declare @.s as char(6)
set @.s=TIME

select cast(substring(@.s,1,1) as int)*60+cast(substring(@.s,2,2) as int)+ cast(substring(@.s,4,3) as dec(10,5))/600

Results:
Server: Msg 207, Level 16, State 3, Line 2
Invalid column name 'TIME'.

I wasn't able to make it work.....

Thanks...|||No, no, no. I assume that you have a table T with a field TIME. All you have to do is to say

SELECT TIME, cast(substring(Time,1,1) as int)*60+cast(substring(Time,2,2) as int)+ cast(substring(Time,4,3) as dec(10,5))/600
FROM T|||Hi DoktorBlue, I know you are tired of me, right? :>(

Tryed:
SELECT TIME, cast(substring(Time,1,1) as int)*60+cast(substring(Time,2,2) as int)+ cast(substring(Time,4,3) as dec(10,5))/600
FROM sample2

Returned:
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.
Server: Msg 256, Level 16, State 1, Line 1
The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

sample2 table has the fields:
# VARCHAR 255
Access# VARCHAR 255
Time INT
Cost INT

Do I need to declare?

Thanks again.

Thanks.|||Hi Andre,

Slowly, but we getting somewhere ....

Your error message indicates, that your TIME field isn't a CHAR, but an INT. So, replace all occurences of

substring(Time,

by

substring(right('000000' + cast(TIME as VARCHAR(6)), 6),|||Hi there, I got this code from another colleague that worked fine:

SELECT access#,
CAST(SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) +
CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) +
(CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)*60)) AS DECIMAL(8,2)) AS TIME,
SUM(cost) AS PRICE,
CAST((SUM(cost) / SUM(((CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-2, 3) AS DECIMAL)/10)/60) +
CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-4, 2) AS INT) +
(CAST(SUBSTRING(CAST(time AS CHAR(7)), LEN(time)-6, 2) AS INT)*60))) AS DECIMAL(8,2)) AS MONEY
FROM sample
GROUP BY access#

Thank you all!

Andre Mori

convert number to date datatype

easy question here.

I have:
0123
0125
0227
0327
04..
05..
06..
07..
08..
09..
..
1231

The first two numbers is the month and the second two numbers is the day of the month.

I need to display 01 as Jan, 02 as Feb and so on.

left(field1, 2) -- I know this grabs the first two numbers, but how do I convert it to a date data type?dateadd(dd,convert(int,right(field1,2)),dateadd(mm ,convert(int,left(field1,2)),'19000101'))

CONVERT Float to char

We have a table that was imported from Access to SQL 2000. For some reason
the old data had phone numbers set as Float. ? If anyone can explain that
I'd love to hear it.
Anyway, I have a SQL 2000 table that has a 10 character column called
Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
just get the 10 character phone converted from Float to char?
Thank you,
AnthonyUse the STR Function STR(F, N, R) ,
Where F is any numeric value,
N is integer total number of characters you want output, and
R is integer Number of characters to right of decimal point
as example
Declare @.F Float Set @.F = 8052080080
Select @.F, Str(@.F, 10, 0)
"Anthony W DiGrigoli" wrote:

> We have a table that was imported from Access to SQL 2000. For some reason
> the old data had phone numbers set as Float. ? If anyone can explain tha
t
> I'd love to hear it.
> Anyway, I have a SQL 2000 table that has a 10 character column called
> Phone_No and when I perform the INSERT I get 7.12345+009. What can I do to
> just get the 10 character phone converted from Float to char?
> Thank you,
> Anthony

Convert digits to letters

I want to know if somebody have a fucntion to convert numbers to letters.
for Example: 56 to Fifty Six or
10,599 to Ten Thousand five hundred and ninety nine.
Please help me...
Radhamescheck this link
[url]http://www.novicksoftware.com/UDFofW/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWords
.htm[/url]
-Omnibuzz
--
Please post ddls and sample data for your queries and close the thread if
you got the answer for your question.
"Radhames" wrote:

> I want to know if somebody have a fucntion to convert numbers to letters.
> for Example: 56 to Fifty Six or
> 10,599 to Ten Thousand five hundred and ninety nine.
> Please help me...
> Radhames|||Thanks omnibuzz
I going to implement this function in my database to convert numbers
to Spanish Words.
:)
"Omnibuzz" wrote:
> check this link
> [url]http://www.novicksoftware.com/UDFofW/Vol2/T-SQL-UDF-Vol-2-Num-9-udf_Num_ToWor
ds.htm[/url]
> --
> -Omnibuzz
> --
> Please post ddls and sample data for your queries and close the thread if
> you got the answer for your question.
>
> "Radhames" wrote:
>|||Don't forget to select the fact that he answered your question. Its nice to
see the little green check mark next to the question once it has been
answered.
:)
"Radhames" wrote:
> Thanks omnibuzz
> I going to implement this function in my database to convert numbers
> to Spanish Words.
> :)
> "Omnibuzz" wrote:
>|||> Don't forget to select the fact that he answered your question. Its nice
> to
> see the little green check mark next to the question once it has been
> answered.
What little green check mark?
Please keep in mind we're not all using a frilly web gui.|||A common function in report writers converts numbers into words so that
they can be used to print checks, legal documents and other reports.
This is not a common function in SQL products, nor is it part of the
standards.
A method for converting numbers into words using only standard SQL by
Stu Bloom follows. This was posted on 2002 Jan 02 on the SQL Server
Programming newsgroup.
First, create a table
CREATE TABLE NbrWords
(number INTEGER PRIMARY KEY,
word VARCHAR(30) NOT NULL);
Then populate it with the literal strings of all NbrWords from 0 to
999. Assuming that your range is 1 - 999,999,999 use the following
query; it should be obvious how to extend it for larger numbers and
fractional parts.
CASE WHEN :num < 1000
THEN (SELECT word FROM NbrWords
WHERE number = :num)
WHEN :num < 1000000
THEN (SELECT word FROM NbrWords
WHERE number = :num / 1000)
|| ' thousand '
|| (SELECT word FROM NbrWords
WHERE MOD (number = :num, 1000))
WHEN :num < 1000000000
THEN (SELECT word FROM NbrWords
WHERE number = :num / 1000000)
|| ' million '
|| (SELECT word FROM NbrWords
WHERE number = MOD((:num / 1000), 1000))
|| CASE WHEN MOD((:num / 1000), 1000) > 0
THEN ' thousand '
ELSE '' END
|| (SELECT word FROM NbrWords
WHERE number = MOD(:num, 1000))
END;