converting decimal to time

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

-PatP|||What Pat said

DECLARE @.hours decimal(15,4)
SELECT @.hours = 28.5
SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@.hours)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60)-FLOOR(((@.hours-FLOOR(@.hours))*60)))*60),2)|||Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:declare @.Hours decimal (6, 4)
set @.Hours = 28.5
select dateadd(minute, @.Hours * 60, 0)|||I was gonna give them that, but I realized it wasn't what they asked for...

Hours of what BTW...sounds like derived data gotta be careful with that

Converting Data Types

I need to be able to convert a numeric data type to a text data type.

I've tried the TO_CHAR and the DBCONVERT functions but I am returned an error stating that they are not recognized functions.

I'm using the Query Analyzer on a Windows 2000 SQL Server.

Any help would be appreciated.

TechRickYou can use the cast or convert functions.|||Thanks,

I was just reading up on the CAST function. However, I've not been able to make it work yet.

In correction to my original post, I need to take a MONEY data type and convert it to a TEXT data type. I hope this possible.

Thanks again,

TechRick|||Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles|||You can also do it this way:

select cast(cast(price as varchar(30)) as text) from titles

But since the conversion is implicit between varchar and text you don't need to do this.|||Thanks again for the help. Your suggestion works great but when I try to update the data I get this:

Server: Msg 260, Level 16, State 1, Line 31
Disallowed implicit conversion from data type varchar to data type money, table..., column 'PRICE'. Use the CONVERT function to run this query.
Server: Msg 257, Level 16, State 1, Line 31
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

I'll let you know once I get it worked out.

Best Regards,
TechRick|||The way the error is reading is that you are trying to put a varchar into a money column - is that correct ?|||Yes, I have a large list of items collected from various tables and there are prices associated with these items. A good majority of these items have no price (.0000) and I would like to exchange the .0000 price for a 'CALL' or something similar.

I could not update the records with a "text" substitute so I thought I would convert the data type to TEXT so I can plug in whatever I want.

Thanks again,
TechRick|||Why did you choose text over varchar ? Could you post your update statement as well as the definition of the table you are updating ? You have to explicitly convert varchar to money and vice-versa using either cast or convert. But I am a little confused about the first error you received - why are you trying to put a varchar into a money column ?|||You'll have to excuse me, I've only been working with SQL for less than 3 weeks. I've been learning as I go (with my Dummies and Sam's books and this forum). I've already written a few complex queries and as for this particular situation, this is the last obsticle I need to overcome to put this query to rest.

Based on the error I was getting I assumed that I was doing something wrong but I didn't have the time to completely research it. I'll pick it back up on Monday. I think I need to understand the convert and cast functions better before I can make use of them. I'll be working towards that end.

As for now, I'm away from work and don't have easy access to the code. I'll post it Monday after I tinker a little more.

Thanks for the help.

TechRick|||Ah, by "convert" you mean that you would like to change the data type of a column from money to varchar so that you can store a mix of data in a single column. The easy answer is to just change the data type of the column in Enterprise Manager. If SQL Server can find a reasonable way to preserve the data, it will. Thereafter you can store any character data in the column, e.g. "Call" or "Operators are standing by!".

The "correct" answer is rather different, and a valid subject for debate. If you do not have a price for an item, the correct representation in the database should be different from a free item. For example, you might use the value NULL to indicate "call for price" and $0.00 for a free item. Alternatively, it may make more sense in your application to have a separate means of flagging items for which you don't want to publish a price, qualify for free shipping, have quantity discounts, ... . More columns or tables may be needed.|||Agreed about the 'correct' answer. Fortunately and unfortunately, I didn't write the application so I'm working with what's there.

I managed to get it taken care of by adding the convert on my select statment.

SELECT UPPER(ITEM), DESCRIPT, Q_STK, QTY_RESERVE, CAST (SELLPRICE AS VARCHAR(15)),...

By doing it this way I am able to manipulate the data any way I want.
Add 'CALL', 'FREE', etc.

Thanks all for the help. One last item and this query is behind me.

Best Regards,
TechRick|||Originally posted by rnealejr
Try the following from the pubs database and titles table:

SELECT price, cast(price AS varchar(30)) FROM titles

rnealejr, I noticed as I was going back through all these posts that you were right on the money a long time ago! Thanks for the help. I think I was trying to perform an update on the column using the cast rather than taking the data in converted from the start. Anyhow, just wanted to thank you for your help. Too bad I had to learn the hard way.

Best Regards,
TechRick|||Thanks for the email and compliment as well as a good pun (right on the money) - I enjoyed that.

Good luck.|||Another handy tool for fudging return values within a query is throwing in a CASE, e.g.:

select Description, ServingSize, case when Price<>0.0 then Convert(VarChar,Price) else 'Call' end as 'AdvertisedPrice'
from PiecesParts where Fused=1

Note that there are two slightly different versions of CASE. One lets you test a single expression against multiple values, while the other lets you test multiple expressions.

You can swindle a lot of logic into a CASE or nested CASEs. For example, it could check for quantity price breaks or apply discounts based on data from other tables or variables. The result is just another (computed) column in the recordset returned from the query. (As such, it isn't writable.)

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 a number into a date type using an expression

I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!

Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.

Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.

I'm just getting started with SQL 2005 so I don't know how to do this using an expression.

Any help would be greatly appreciated.

Thanks,

Gray

I think your approach sounds valid. You're going to have to parse it with substring and build it into a date format. Then you can cast the string to a date/time field.

(DT_DBTIMESTAMP)(substring([YourColumn + 19000000],x,y) + "/" + .......)

|||

Hi Phil,

Thanks for the info ... it really helped ... here's my final expression ...

(DT_DBTIMESTAMP)(SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),5,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),7,2) + "/" + SUBSTRING(((DT_STR,8,1252)(YearMonthDay + 19000000)),1,4))

Thanks again,

Gray

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

convert varchar to numeric(4,2)

Hi,

I have two databases SQL Server. I′m migrating tables from one database to other,
but some columns are diferent data types.

Is there a way to convert varchar to numeric(4,2) like follows:

select convert(numeric(4,2), discounting)
from database1.dbo.table1

the following error occurs:

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

How can I do this?

thanks!!!!

The error message indicates that you have values in the column that cannot be converted to numeric successfully. You could use ISNUMERIC to check for such values and filter them but it may not be entirely accurate (since ISNUMERIC checks for several numeric type conversions, money and integer conversions). In the simple case, you could write your SELECT statement like:

select case isnumeric(discounting) when 1 then convert(numeric(4,2), discounting) end
from database1.dbo.table1

|||

ivision.wordpress.com/2006/12/05/custom-function-to-convert-varchar-to-int/

sqlsql

Convert varchar to numeric

I have a varchar value like -2.47382558882236E-10. How can I convert this
to -2.47382558882236 in order to then convert to numeric. Logically I want
to truncate everything after the 14th digit to the right of the decimal
point.
Thanks for any help.Try,
select cast(cast('-2.47382558882236E-10' as float) as decimal(16, 14))
go
AMB
"Terri" wrote:

> I have a varchar value like -2.47382558882236E-10. How can I convert this
> to -2.47382558882236 in order to then convert to numeric. Logically I want
> to truncate everything after the 14th digit to the right of the decimal
> point.
> Thanks for any help.
>
>

Convert varchar to decimal

Hello, I am running a SELECT statement on a varchar 50 field. I would like
to convert the output to a numeric field with 2 decimal places. I tried a
CAST but that didn't work. Any suggestions would be appreciated. Thanks,
Pancho."Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.
Not knowing the largest decimal value you might wish to display, you could
do something like this:
SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
FROM TableName
This will allow a total of 10 digits with 2 on the right of the decimal
point.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Pancho,
What didn't work?
Did you receive an error message?
If so, what?
If not, show us your code.
"Pancho" <Pancho@.discussions.microsoft.com> wrote in message
news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work. Any suggestions would be appreciated. Thanks,
> Pancho.|||Rick,
I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
and got:
'dec' is not a recognized function name
Got the same error using 'decimal'. I am running a successful
SELECT CONVERT (CHAR (8), Field8, 112) AS Field8Text but the dec didn't work
.
"Rick Sawtell" wrote:

> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
> Not knowing the largest decimal value you might wish to display, you could
> do something like this:
> SELECT CONVERT(<varchar field>, dec(10,2)) AS FieldName
> FROM TableName
>
> This will allow a total of 10 digits with 2 on the right of the decimal
> point.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Hi Raymond,
I got the msg "Unable to convert varchar field" when using CAST. I am
taking data from a varchar and trying to format it to a numeric field, 10
wide with 2 decimal spaces. Pls see code above in my reply to Rick. Thanks
,
Pancho.
"Raymond D'Anjou" wrote:

> Hi Pancho,
> What didn't work?
> Did you receive an error message?
> If so, what?
> If not, show us your code.
> "Pancho" <Pancho@.discussions.microsoft.com> wrote in message
> news:41493A88-38CF-4F3C-8237-34B51AF3202F@.microsoft.com...
>
>|||> Hello, I am running a SELECT statement on a varchar 50 field. I would
> like
> to convert the output to a numeric field with 2 decimal places. I tried a
> CAST but that didn't work.
What does "didn't work" mean? What did you try? Are you sure all of the
values are really numeric and can be converted? If so, why are you storing
them in a VARCHAR(50) column?|||> I tried CONVERT(FieldValue7, (dec(10,2)) AS NewFieldName
> and got:
> 'dec' is not a recognized function name
Did you try using the right syntax?
NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)|||> I got the msg "Unable to convert varchar field" when using CAST.
Well, because you decided to use a VARCHAR(50) to store decimals, invariably
you will get varchar data that is not a decimal.
You shouldn't be really surprised by this.
A start would be to filter out the rows where ISNUMERIC(column_name) = 0.
However, ISNUMERIC() is not perfect either, see http://www.aspfaq.com/2390
Then, try cleaning up your data and fixing the data type. When you do that,
you won't need to do a convert at all.|||You got me. I didn't design this DB; a vendor did. I would not have stored
a field which has cash values as a varchar either. I'm trying to convert it
from one vendor to another.
Thanks everyone for your comments. I'm closing this one now.
"Aaron Bertrand [SQL Server MVP]" wrote:

> What does "didn't work" mean? What did you try? Are you sure all of the
> values are really numeric and can be converted? If so, why are you storin
g
> them in a VARCHAR(50) column?
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OfuNFSyXGHA.4388@.TK2MSFTNGP03.phx.gbl...
> Did you try using the right syntax?
> NewFieldName = CONVERT(DECIMAL(10,2), FieldValue7)
>
Ooops.. Got it backwards. Thanks Aaron.
Rick

convert to set-based

I need help understanding this code and seeing if it can be converted to
set-based.
Declare @.Id int
Declare @.companyId numeric(18,0)
Declare @.franchiseId numeric(18,0)
--I understand this part where a table is created and populated
Declare @.tblCompanyFranchise Table( [Id] int, companyId numeric(18,0),
franchiseId numeric(18,0))
Declare @.pinId Varchar(30)
insert into @.tblCompanyFranchise([Id],companyId , franchiseId )
select [ID],numCompanyId,numFranchiseId from dbo.tblCompanyFranchise
where pinId is null
While(SELECT COUNT(*) FROM @.tblCompanyFranchise) > 0
Begin
--I don't understand where [id], companyid and franchiseid come from here to
compare to what is in the temp @.tblcompanyfranchise table
SELECT TOP 1 @.Id=[id], @.companyId=companyId,@.franchiseId=franch
iseId FROM
@.tblCompanyFranchise
--here deleting row just selected above
DELETE FROM @.tblCompanyFranchise WHERE companyId=@.companyId
and franchiseId=@.franchiseId and [id] =@.id
--not sure purpose of attempts here
Declare @.attempts int
Set @.attempts = 10
while(@.attempts > 0)
Begin
--set @.pinid as unique
--not sure why add @.id to part of pinid
Set @.pinId = Abs(CheckSum(NEWID()))
Set @.pinId = cast ( (cast (substring(@.pinId,1,10-len(@.Id)) as varchar) +
cast (@.Id as varchar)) as Varchar)
--this part below I don't understand. Is the update statement at the bottom
outside of the if statement below? What is the purpose of the attempts? IF
the count is not > 0 then attempts are set to 0. Correct?
if((Select count(*) from dbo.tblCompanyFranchise where pinid = @.pinId) > 0)
Begin
Set @.attempts = @.attempts - 1
If @.attempts = 0
Set @.pinId = null
End
Else
Begin
Set @.attempts = 0
End
End
Update dbo.tblCompanyFranchise set pinId = @.pinId
where numCompanyId=@.companyId and numFranchiseId=@.franchiseId and [id] =@.id
End
Go
Is there a set-based way to do the same thing?Looks to me like it's trying to create a pin for each companyid, but it's
worried that the pin might not be unique. To combat that, they put the
companyid at the end of it, padding to 10 characters. I suppose then though,
they might have the situation where there are two pins the same, if the
companyids are, say, 102 and 3102.
I would suggest that the logic be investigated, and replaced with something
that is going to produce a unique string each time, so that it doesn't need
to do each one individually. For example, if you are allowed a pin of 30
characters (as per the declare statement), then why not use all 10
characters of the random number (pad it out if necessary) and then put the
id on the end. That way, there will never be an overlap, as the digits from
position 11 on would be unique (just longer for larger numbers).
If the pin has to be 10 characters, then perhaps you could put a hyphen in
before the companyid section?
If all the characters have to be digits, then perhaps pad the companyid out
to a known number of digits - but that will restrict the number of companies
you could have in the system.
Of course, the chance of an overlap is really quite small, so you could put
a unique index on the pinid field, and just retry the query if you get an
error.
update dbo.tblCompanyFranchise
set pinId = cast ( (cast (substring(cast(abs(CheckSum(NEWID())) as
varchar),1,10-len(Id)) as varchar) + cast (Id as varchar)) as Varchar)
where pinId is null
Rob
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B80E4114-572B-4B03-9095-99BC70A780E3@.microsoft.com...
>I need help understanding this code and seeing if it can be converted to
> set-based.
> Declare @.Id int
> Declare @.companyId numeric(18,0)
> Declare @.franchiseId numeric(18,0)
> --I understand this part where a table is created and populated
> Declare @.tblCompanyFranchise Table( [Id] int, companyId numeric(18,0),
> franchiseId numeric(18,0))
> Declare @.pinId Varchar(30)
> insert into @.tblCompanyFranchise([Id],companyId , franchiseId )
> select [ID],numCompanyId,numFranchiseId from dbo.tblCompanyFranchise
> where pinId is null
> While(SELECT COUNT(*) FROM @.tblCompanyFranchise) > 0
> Begin
> --I don't understand where [id], companyid and franchiseid come from here
> to
> compare to what is in the temp @.tblcompanyfranchise table
> SELECT TOP 1 @.Id=[id], @.companyId=companyId,@.franchiseId=franch
iseId FROM
> @.tblCompanyFranchise
> --here deleting row just selected above
> DELETE FROM @.tblCompanyFranchise WHERE companyId=@.companyId
> and franchiseId=@.franchiseId and [id] =@.id
> --not sure purpose of attempts here
> Declare @.attempts int
> Set @.attempts = 10
> while(@.attempts > 0)
> Begin
> --set @.pinid as unique
> --not sure why add @.id to part of pinid
> Set @.pinId = Abs(CheckSum(NEWID()))
> Set @.pinId = cast ( (cast (substring(@.pinId,1,10-len(@.Id)) as varchar) +
> cast (@.Id as varchar)) as Varchar)
> --this part below I don't understand. Is the update statement at the
> bottom
> outside of the if statement below? What is the purpose of the attempts? IF
> the count is not > 0 then attempts are set to 0. Correct?
> if((Select count(*) from dbo.tblCompanyFranchise where pinid = @.pinId) >
> 0)
> Begin
> Set @.attempts = @.attempts - 1
> If @.attempts = 0
> Set @.pinId = null
> End
> Else
> Begin
> Set @.attempts = 0
> End
> End
> Update dbo.tblCompanyFranchise set pinId = @.pinId
> where numCompanyId=@.companyId and numFranchiseId=@.franchiseId and [id]
> =@.id
> End
> Go
> Is there a set-based way to do the same thing?
> --
>|||I thought newid() always produced a unique value? Do you know what the
purpose of the 10 "attempts" toward the bottom was?
Thanks,
--
Dan D.
"Rob Farley" wrote:

> Looks to me like it's trying to create a pin for each companyid, but it's
> worried that the pin might not be unique. To combat that, they put the
> companyid at the end of it, padding to 10 characters. I suppose then thoug
h,
> they might have the situation where there are two pins the same, if the
> companyids are, say, 102 and 3102.
> I would suggest that the logic be investigated, and replaced with somethin
g
> that is going to produce a unique string each time, so that it doesn't nee
d
> to do each one individually. For example, if you are allowed a pin of 30
> characters (as per the declare statement), then why not use all 10
> characters of the random number (pad it out if necessary) and then put the
> id on the end. That way, there will never be an overlap, as the digits fro
m
> position 11 on would be unique (just longer for larger numbers).
> If the pin has to be 10 characters, then perhaps you could put a hyphen in
> before the companyid section?
> If all the characters have to be digits, then perhaps pad the companyid ou
t
> to a known number of digits - but that will restrict the number of compani
es
> you could have in the system.
> Of course, the chance of an overlap is really quite small, so you could pu
t
> a unique index on the pinid field, and just retry the query if you get an
> error.
> update dbo.tblCompanyFranchise
> set pinId = cast ( (cast (substring(cast(abs(CheckSum(NEWID())) as
> varchar),1,10-len(Id)) as varchar) + cast (Id as varchar)) as Varchar)
> where pinId is null
> Rob
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B80E4114-572B-4B03-9095-99BC70A780E3@.microsoft.com...
>
>|||>> s there a set-based way to do the same thing? <<
What is this nightmare of poorly formatted insanely proprietary code
supposed to do? Without a spec, it is pretty hard to answer your
querstion. I have the feeling that this crap is not using a relatioanl
key at all, but that it is randomly trying to construct an unverifiable
exposed locator on the fly.|||Dan,

>I thought newid() always produced a unique value?
It does. But your code isn't using it in its standard form. It's grabbing
its checksum, and its absolute value, so that just makes it a random number
less than 2^31.
But then you change the last characters of that number with the id from the
table. Eg... if you have an id of 100342, your 10-digit random number might
be:
2834100342
But the whole reason for doing it one by one is that your code is worried
that it might not be unique. But it's going to be _probably_ unique - the
only chance of overlaps is where you have two numbers that overlap already.
For example, there's a 1/1000 chance that the same number above could be
generated for company 4100342. And it figures that it should be able to find
a unique number some time in the first 10 tries - which it shouldn't have
any problem doing at all.
The chance of each one being unique is very high. Not high enough to warrant
doing each one individually and checking each time. But if you need it to be
enforced, then do it with a unique key, and just put a check in to see that
the update hasn't broken the rule. If it has, just re-run it.
Let's have a quick think about where the possible overlaps are:
Record 100342 could overlap with:
record 2 (1/1000000000 chance)
record 42 (1/100000000 chance)
record 342 (1/10000000 chance)
record 1100342 (1/1000 chance)
record 2100342 (1/1000 chance)
...etc
If there's a really good business reason for the uniqueness, this is enough
of a risk to make it worth enforcing, but you could update hundreds of
thousands of records at a time without noticing any overlaps.
Rob
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:F8787F1F-4AC0-405C-9E8E-5ACF7841F11D@.microsoft.com...
>I thought newid() always produced a unique value? Do you know what the
> purpose of the 10 "attempts" toward the bottom was?
> Thanks,
> --
> Dan D.
>
> "Rob Farley" wrote:
>|||The pinid column is a varchar(30) so if the contractor was really worried
about uniqueness, I don't know why he didn't use more of the id field. The i
d
itself is also supposed to be unique so the chance of combining the checksum
of newid() and all of the id field is pretty small.
I ran your query and it took 6 seconds. The original code took 4 hours.
Can you tell me what how this part of the code works:
SELECT TOP 1 @.Id=[id], @.companyId=companyId,@.franchiseId=franch
iseId FROM
@.tblCompanyFranchise
I interpret it to mean select the first record from @.tblCompanyFranchise
where the @.id in @.tblCompanyFranchise equals [id] and where @.companyid equals
companyid and where @.franchiseid equals franchiseid. But where does the valu
e
for [id], companyid and franchiseid come from?
Thanks so much for your help Rob.
Dan D.
"Rob Farley" wrote:

> Dan,
>
> It does. But your code isn't using it in its standard form. It's grabbing
> its checksum, and its absolute value, so that just makes it a random numbe
r
> less than 2^31.
> But then you change the last characters of that number with the id from th
e
> table. Eg... if you have an id of 100342, your 10-digit random number migh
t
> be:
> 2834100342
> But the whole reason for doing it one by one is that your code is worried
> that it might not be unique. But it's going to be _probably_ unique - the
> only chance of overlaps is where you have two numbers that overlap already
.
> For example, there's a 1/1000 chance that the same number above could be
> generated for company 4100342. And it figures that it should be able to fi
nd
> a unique number some time in the first 10 tries - which it shouldn't have
> any problem doing at all.
> The chance of each one being unique is very high. Not high enough to warra
nt
> doing each one individually and checking each time. But if you need it to
be
> enforced, then do it with a unique key, and just put a check in to see tha
t
> the update hasn't broken the rule. If it has, just re-run it.
> Let's have a quick think about where the possible overlaps are:
> Record 100342 could overlap with:
> record 2 (1/1000000000 chance)
> record 42 (1/100000000 chance)
> record 342 (1/10000000 chance)
> record 1100342 (1/1000 chance)
> record 2100342 (1/1000 chance)
> ...etc
> If there's a really good business reason for the uniqueness, this is enoug
h
> of a risk to make it worth enforcing, but you could update hundreds of
> thousands of records at a time without noticing any overlaps.
> Rob
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:F8787F1F-4AC0-405C-9E8E-5ACF7841F11D@.microsoft.com...
>
>|||I wish I knew. This project was contracted out. There was no statement of
work, documentation, etc. by the original contractors. The project was takin
g
too long (surprise!) so it was brought in-house and another group of
contractors was hired to fix it.
I look through the code once in a while to see how other people code and to
learn.
--
Dan D.
"--CELKO--" wrote:

> What is this nightmare of poorly formatted insanely proprietary code
> supposed to do? Without a spec, it is pretty hard to answer your
> querstion. I have the feeling that this crap is not using a relatioanl
> key at all, but that it is randomly trying to construct an unverifiable
> exposed locator on the fly.
>|||Dan,
It sounds to me like you need to look through the business rules, and
probably get new contractors. :) If you can use more than 10 digits, then by
all means do that. I would actually suggest starting with the id number and
then using the large number padded to 10 digits. That way, you can guarantee
its uniqueness, plus you won't have 0 as the first character (because you
will need to pad the 10-digits to be sure it's unique - consider the case
where your checksum gives you a very small result).

> Can you tell me what how this part of the code works:
> SELECT TOP 1 @.Id=[id], @.companyId=companyId,@.franchiseId=franch
iseId FROM
> @.tblCompanyFranchise
This just gets the top row from @.tblCompanyFranchise without having any
filter, and populates the variables @.Id, @.companyId and @.franchiseId. It's
basically a cursor without having a cursor. My guess is that your
contractors have read that cursors are bad practice, but instead of taking a
set-based approach, have simply altered the code to remove the cursor
declaration.
Rob
PS: Sorry for my silence over the past several hours - I'm in Australia.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A69B97BF-8B5F-4BFE-AD9E-573868DBBAD7@.microsoft.com...
> The pinid column is a varchar(30) so if the contractor was really worried
> about uniqueness, I don't know why he didn't use more of the id field. The
> id
> itself is also supposed to be unique so the chance of combining the
> checksum
> of newid() and all of the id field is pretty small.
> I ran your query and it took 6 seconds. The original code took 4 hours.
> Can you tell me what how this part of the code works:
> SELECT TOP 1 @.Id=[id], @.companyId=companyId,@.franchiseId=franch
iseId FROM
> @.tblCompanyFranchise
> I interpret it to mean select the first record from @.tblCompanyFranchise
> where the @.id in @.tblCompanyFranchise equals [id] and where @.companyid
> equals
> companyid and where @.franchiseid equals franchiseid. But where does the
> value
> for [id], companyid and franchiseid come from?
> Thanks so much for your help Rob.
> --
> Dan D.
>
> "Rob Farley" wrote:
>|||:) Yup. I try to sleep at night occasionally.
"Stefan Berglund" <sorry.no.koolaid@.for.me> wrote in message
news:ges982dt67flvs05h9i7pjf0sja9ah6i0j@.
4ax.com...
> On Tue, 6 Jun 2006 11:46:47 +0930, "Rob Farley" <rob_farley@.hotmail.com>
> wrote:
> in <uVR#E9QiGHA.3904@.TK2MSFTNGP02.phx.gbl>
> Oh! - Have they been closed for several hours?
> --
> Stefan Berglund|||Yeah, we have a regular scheduled outage for maintenance. We shut down
the country for a few hours each night - didn't you get the memo?
*mike hodgson*
http://sqlnerd.blogspot.com
Stefan Berglund wrote:

>On Tue, 6 Jun 2006 11:46:47 +0930, "Rob Farley" <rob_farley@.hotmail.com>
>wrote:
> in <uVR#E9QiGHA.3904@.TK2MSFTNGP02.phx.gbl>
>
>Oh! - Have they been closed for several hours?
>--
>Stefan Berglund
>

Convert String to Numeric without Decimal

Hi,

I am using Crystal Report 8.5 with Visual Basic 6.0 and MS Access database. Now situation is I have to display three different values for a particular condition. If data is there then number of records else zero and if even no depedend entry is there then "NA".

Variable is numeric and formula is

if {temp.var1} = 0 and {temp.var11} = 0 then 'NA'
else if {temp.var11} = 1 then '0'
else CStr ({temp.var1})

NOW problem is if I am using this report on a system having crystal report installed and set the numeric value as no decimal, it is working fine but when I try the same program on the system on which crystal is not installed it is showing decimal values upto 2 digits. I have used Report Expert Distribution also.

Any idea how to solve this problem. Thanks

VishalFormat that format field number as you want|||What does it mean. Could you please help me out by writing a example code for that.

thanks|||Roght click the field, select format section then goto number and select the required format

Convert String to Numeric

Hi all,

I defined an user string type varible in the package as AccountLen. I am trying to use this varible in the Expression of Derived Column transformation.

I want to retrieve a part of column, i.e: Right(Column1, @.AccountLen), this is always wrong because the AccountLen is string type. How I can convert it to the numeric so that can be used in the RIGHT function?

Thanks

To convert a string to numeric you CAST the field.

(DT_I4)@.[User::AccountLen]

However, I don't believe that will work as I don't believe most functions accept variables as input parameters.

|||

Thank you very much. Your solution is working fine.

Convert string to numeric

I have a field which in the db is is char, and I want to sort in the report
numerically. Tried changing it in many areas but it still sorts as if alpha.
Is there an expression I can use on the report field to convert it to
numeric? Will cast or convert work in a field level experssion?Edit Field and make it like Cint(Fields!MyField.Value)
Then sort on this field.
- Suneet Mohan
"Chris Patten" wrote:
> I have a field which in the db is is char, and I want to sort in the report
> numerically. Tried changing it in many areas but it still sorts as if alpha.
> Is there an expression I can use on the report field to convert it to
> numeric? Will cast or convert work in a field level experssion?
>

Convert Seconds to Time

Alright... i have a field Browse_Time which is in seconds... just a plain
numeric value... such as 1664 which in the real world would be
approximately 28 minutes how do I convert seconds to hours:minutes' I am
clueless...Carlos Rapa wrote:
> Alright... i have a field Browse_Time which is in seconds... just a
> plain numeric value... such as 1664 which in the real world would
> be approximately 28 minutes how do I convert seconds to
> hours:minutes' I am clueless...
Hi Carlos
use the code below in customCode
Public Function TimeString(Seconds As Long, Optional Verbose _
As Boolean = False) As String
'if verbose = false, returns
'something like
'02:22.08
'if true, returns
'2 hours, 22 minutes, and 8 seconds
Dim lHrs As Long
Dim lMinutes As Long
Dim lSeconds As Long
lSeconds = Seconds
lHrs = Int(lSeconds / 3600)
lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
lSeconds = Int(lSeconds Mod 60)
Dim sAns As String
If lSeconds = 60 Then
lMinutes = lMinutes + 1
lSeconds = 0
End If
If lMinutes = 60 Then
lMinutes = 0
lHrs = lHrs + 1
End If
sAns = Format(CStr(lHrs), "#####0") & ":" & _
Format(CStr(lMinutes), "00") & "." & _
Format(CStr(lSeconds), "00")
If Verbose Then sAns = TimeStringtoEnglish(sAns)
TimeString = sAns
End Function
Private Function TimeStringtoEnglish(sTimeString As String) _
As String
Dim sAns As String
Dim sHour, sMin As String, sSec As String
Dim iTemp As Integer, sTemp As String
Dim iPos As Integer
iPos = InStr(sTimeString, ":") - 1
sHour = Left$(sTimeString, iPos)
If CLng(sHour) <> 0 Then
sAns = CLng(sHour) & " hour"
If CLng(sHour) > 1 Then sAns = sAns & "s"
sAns = sAns & ", "
End If
sMin = Mid$(sTimeString, iPos + 2, 2)
iTemp = sMin
If sMin = "00" Then
sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
Else
sTemp = IIf(iTemp = 1, " minute", " minutes")
sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
sAns = sAns & Format$(iTemp, "##") & sTemp
End If
iTemp = Val(Right$(sTimeString, 2))
sSec = Format$(iTemp, "#0")
sAns = sAns & sSec & " second"
If iTemp <> 1 Then sAns = sAns & "s"
TimeStringtoEnglish = sAns
End Function
* Source www.freevbcode.com*
regards
Frank|||I feel kind of dumb but where is the customCode area? is it where I right
click on the field click properties? and towards the right hand side there is
Custom: with the button and blank textbox? if so I put it in there and
the field just comes up with the formula...
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Alright I figured out where the customcode is but unfortunately i keep getting
####0:00.0 as a result...
i have the field set up as
code.TimeString(Sum(Fields!browse_time.Value))
are there anyother settings i am forgetting'
Thanks a bunch,
Carlos
"Frank Matthiesen" wrote:
> Carlos Rapa wrote:
> > Alright... i have a field Browse_Time which is in seconds... just a
> > plain numeric value... such as 1664 which in the real world would
> > be approximately 28 minutes how do I convert seconds to
> > hours:minutes' I am clueless...
>
> Hi Carlos
> use the code below in customCode
> Public Function TimeString(Seconds As Long, Optional Verbose _
> As Boolean = False) As String
> 'if verbose = false, returns
> 'something like
> '02:22.08
> 'if true, returns
> '2 hours, 22 minutes, and 8 seconds
> Dim lHrs As Long
> Dim lMinutes As Long
> Dim lSeconds As Long
> lSeconds = Seconds
> lHrs = Int(lSeconds / 3600)
> lMinutes = (Int(lSeconds / 60)) - (lHrs * 60)
> lSeconds = Int(lSeconds Mod 60)
> Dim sAns As String
>
> If lSeconds = 60 Then
> lMinutes = lMinutes + 1
> lSeconds = 0
> End If
> If lMinutes = 60 Then
> lMinutes = 0
> lHrs = lHrs + 1
> End If
> sAns = Format(CStr(lHrs), "#####0") & ":" & _
> Format(CStr(lMinutes), "00") & "." & _
> Format(CStr(lSeconds), "00")
> If Verbose Then sAns = TimeStringtoEnglish(sAns)
> TimeString = sAns
> End Function
> Private Function TimeStringtoEnglish(sTimeString As String) _
> As String
> Dim sAns As String
> Dim sHour, sMin As String, sSec As String
> Dim iTemp As Integer, sTemp As String
> Dim iPos As Integer
> iPos = InStr(sTimeString, ":") - 1
> sHour = Left$(sTimeString, iPos)
> If CLng(sHour) <> 0 Then
> sAns = CLng(sHour) & " hour"
> If CLng(sHour) > 1 Then sAns = sAns & "s"
> sAns = sAns & ", "
> End If
> sMin = Mid$(sTimeString, iPos + 2, 2)
> iTemp = sMin
> If sMin = "00" Then
> sAns = IIf(Len(sAns), sAns & "0 minutes, and ", "")
> Else
> sTemp = IIf(iTemp = 1, " minute", " minutes")
> sTemp = IIf(Len(sAns), sTemp & ", and ", sTemp & " and ")
> sAns = sAns & Format$(iTemp, "##") & sTemp
> End If
> iTemp = Val(Right$(sTimeString, 2))
> sSec = Format$(iTemp, "#0")
> sAns = sAns & sSec & " second"
> If iTemp <> 1 Then sAns = sAns & "s"
> TimeStringtoEnglish = sAns
> End Function
> * Source www.freevbcode.com*
> regards
> Frank
>
>|||Carlos Rapa wrote:
> code.TimeString(Sum(Fields!browse_time.Value))
> are there anyother settings i am forgetting'
Don't know...are the results ok?
regards
Frank
www.xax.de|||You could use the TimeSpan structure:
=new TimeSpan(0, 0, Fields!Browse_Time.Value).Minutes & ":" & new
TimeSpan(0, 0, Fields!Browse_Time.Value).Seconds
See also:
http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemTimeSpanClassTopic.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Carlos Rapa" <Carlos Rapa@.discussions.microsoft.com> wrote in message
news:FF418AD5-D687-456A-ACA6-BE9D8FD49670@.microsoft.com...
> Alright... i have a field Browse_Time which is in seconds... just a plain
> numeric value... such as 1664 which in the real world would be
> approximately 28 minutes how do I convert seconds to hours:minutes' I
am
> clueless...

Convert numeric data to text possible?

I have a need to covert a number (like 180) to the text version (one hundred
eighty). Is this possible is RS? If so, how?
Thanks!
LeighYou can create custom function using the VB.NET syntax.
Check this page for some examples:
http://msdn.microsoft.com/SQL/sqlwarehouse/ReportingServices/default.aspx?pull=/library/en-us/dnsql2k/html/erscstcode.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Leigh" <Leigh@.discussions.microsoft.com> wrote in message
news:938DE0DF-A3CD-45CB-857B-6CDA722121C8@.microsoft.com...
>I have a need to covert a number (like 180) to the text version (one
>hundred
> eighty). Is this possible is RS? If so, how?
> Thanks!
> Leigh

Convert hexadecimal value to real data type

Hi ,

I want to convert hexadecimal to numeric data type. Using directly Cast or Convert function is not working.

Please suggest an alternative how to retrieve the numeric value of binary data .

Thanks in advance

Regards

Srinivas Govada

Srinivas,

Please show us what does not work for you. The following is

fine:

declare @.n numeric(10,2)

set @.n = 1.23

select cast(@.n as varbinary(20))

-- returns 0x0A0200017B000000

select cast(0x0A0200017B000000 as numeric(10,2))

If you are trying to convert binary representations of

[float] values to [float], you can use this code:

declare @.b binary(8)

set @.b = 0xC094D954FB549F95

declare @.s bit

declare @.e smallint

declare @.m float

set @.s = case when substring(@.b,1,1) >= 0x80 then 1 else 0 end

set @.e = (substring(@.b,1,2)&32752)/16-1022

set @.m = cast(substring(@.b,6,3) as int)/2097152e0/536870912e0

+ (substring(@.b,2,4)&268435455)/536870912e0+0.5e0

select case when @.s = 1 then -1e0 else 1e0 end * @.m * power(2e0,@.e)

Steve Kass

Drew University

www.stevekass.com

Srinivas Govada@.discussions.microsoft.com wrote:

> Hi ,

>

> I want to convert hexadecimal to numeric data type. Using directly Cast

> or Convert function is not working.

>

> Please suggest an alternative how to retrieve the numeric value of

> binary data .

>

> Thanks in advance

>

> Regards

>

> Srinivas Govada

>

>

>

>

Convert decimal to VarChar without trailing 0's?

According to Books online...
"To remove trailing zeros from a result set when you convert from numeric or
decimal data to character data, use the value 128 for style."
So how come this code returns 10.2500000000? And more to the point how do I
get 10.25?
create table #test(
Val Decimal(19,10)
)
Insert into #test( Val ) Values ( 10.25 )
Select
Val,
Convert( VarChar, Val, 128 )
From #test
Any ideas?
Colin.Colin,
Try function STR instead.
select ltrim(str(cast(10.25 as Decimal(19,10)), 8, 2))
AMB
"Colin Dawson" wrote:

> According to Books online...
> "To remove trailing zeros from a result set when you convert from numeric
or
> decimal data to character data, use the value 128 for style."
> So how come this code returns 10.2500000000? And more to the point how do
I
> get 10.25?
> create table #test(
> Val Decimal(19,10)
> )
> Insert into #test( Val ) Values ( 10.25 )
> Select
> Val,
> Convert( VarChar, Val, 128 )
> From #test
>
> Any ideas?
> Colin.
>
>|||That works fine for the specific example, but if I have a number with a
different number of decimal places the results are not correct.
10.125 will get rounded, and this cannot be allowed to happen in the Medical
application that we're developing.
So far, the best solution that I've found is
Replace( RTrim( Replace( Replace( RTrim( Replace( Val, '0', ' ' ) ), ' ',
'0' ), '.', ' ' ) ), ' ', '.' ),
But this is rather CPU intensive. Looks like, I may be starting my own CLR
function library that contains all the stuff that MS missed from SQL2005.
Colin.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:09C08477-DDA9-40C6-8CAF-208AC263B1F5@.microsoft.com...
> Colin,
> Try function STR instead.
> select ltrim(str(cast(10.25 as Decimal(19,10)), 8, 2))
>
> AMB
> "Colin Dawson" wrote:
>

Convert DBs numeric date to SQL datetime

Hi,
I'm moving some data from AS400 DB2 to SQL Server and run
into date conversion problems.
My date from DB2 is numeric 8 characters eg "20031231".
How can I convert it to either datetime or smalldatetime
format in SQL server ?
If my table in SQL is defined as datetime/smalldatetime or
numeric, I will get error during the data pump process.
If I define in char format, data pump works fine but data
isn't the format I want ?
Can someone help ? Thanks.If the date is part of the PK for a fact or dim table, I would use an intege
r key and not datetime. INT or DECIMAL(9,0) should be sufficient for your ne
eds if that is the case. I sometimes import .txt files from AS/400 and the s
ource table must not allow
packed signs. In the activex script transforming the data from source to des
tination you could convert int to datetime or char to int to datetime if you
wish.
You might want to consider importing textfiles and not use the source table
directly since this option gives you a snapshot of the data. If the import f
ails you will always have a source file to check for errors. A transactional
source table might change
and the error be corrected without your knowledge.