Format Question

Hi,
I have a column with numbers like this and the output desired.
dataset output desired
0.170 0.17
0 0
1.120 1.12
0 0
But when i format it with number, #.## etc. i get "0" as 0.00 - which i
dont want
any suggestions ?
Thanks
RPRP,
If I try 0.## I get the results you are looking for. The data type for the
data is dec(5,3). With #.## I get .00 for 0 rather than 0.00. What is the
datatype of your data?
RMac
"RP" wrote:
> Hi,
> I have a column with numbers like this and the output desired.
> dataset output desired
> 0.170 0.17
> 0 0
> 1.120 1.12
> 0 0
> But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> dont want
> any suggestions ?
> Thanks
> RP
>|||I take that back. I'm seeing 0.00 for zero. Let me see what I can come up
with. Apologies.
"RMac" wrote:
> RP,
> If I try 0.## I get the results you are looking for. The data type for the
> data is dec(5,3). With #.## I get .00 for 0 rather than 0.00. What is the
> datatype of your data?
> RMac
>
> "RP" wrote:
> > Hi,
> > I have a column with numbers like this and the output desired.
> >
> > dataset output desired
> > 0.170 0.17
> > 0 0
> > 1.120 1.12
> > 0 0
> >
> > But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> > dont want
> > any suggestions ?
> > Thanks
> > RP
> >|||RP,
Give G5 a try. If you need a larger precision specifier, just change the 5
to the desired number.
RMac
"RP" wrote:
> Hi,
> I have a column with numbers like this and the output desired.
> dataset output desired
> 0.170 0.17
> 0 0
> 1.120 1.12
> 0 0
> But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> dont want
> any suggestions ?
> Thanks
> RP
>|||G5 worked perfect. Thanks RMAC.
(Actually in the meantime i changed it to #.00, and in expression i put an
iif(no <=0 then '0') ..blah blah...
But G5 is much much easier to use. Thanks again.
"RMac" wrote:
> RP,
> Give G5 a try. If you need a larger precision specifier, just change the 5
> to the desired number.
> RMac
> "RP" wrote:
> > Hi,
> > I have a column with numbers like this and the output desired.
> >
> > dataset output desired
> > 0.170 0.17
> > 0 0
> > 1.120 1.12
> > 0 0
> >
> > But when i format it with number, #.## etc. i get "0" as 0.00 - which i
> > dont want
> > any suggestions ?
> > Thanks
> > RP
> >

format datetime without characters

I need to convert the date format with an output of yyyymmdd'
I have tried the following code but it doesn't produce the desired output
CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
Any suggestions?
Thanks in advanceThat should work if supp_creation_date is a DATETIME or SMALLDATETIME. What
result do you get? Can you post some code to reproduce it? What version of
SQL Server? Here's an example:
SELECT CONVERT(VARCHAR(8),CURRENT_TIMESTAMP,112
)
Result:
20050502
(1 row(s) affected)
If you get something else then I'd guess that in the context in which you
are using it the result is being implicitly cast to some other datatype.
David Portas
SQL Server MVP
--|||That seems to work for me:
Declare @.Date DateTime
Set @.Date = Current_TimeStamp
Select Convert(VarChar(8), @.Date, 112)
Produces:
20050502
What output are you getting?
Thomas
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry,
What is the type of column [supp_creation_date]?. If it is not a datetime
then you have to convert it to, before using the statement you posted.
Example:
declare @.s varchar(10)
set @.s = '05/02/2005'
select convert(char(8), convert(datetime, @.s, 101), 112)
go
-- this works
select convert(char(8), getdate(), 112)
go
AMB
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Sherry, Your column in your table must not be a datetime... you'll have to
cast it to a datetime first.
Try this
Select CONVERT(VARCHAR(8), Cast(supp_creation_date As DateTime) , 112 )
From YourTable
"Sherry" wrote:

> I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance|||Does it produce the proper format in Query Analyzer? This is always the
first place to try stuff out, as QA is very good not to insert it's own
formatting to the output:
create table test
(
supp_creation_date datetime
)
insert into test
select getdate()
go
select CONVERT(VARCHAR(8),supp_creation_date , 112 )
from test
returns:
20050502
However, if this value gets put back into a datetime variable before the
client recieves it, or the client puts it inot a date container, the
formatting goes away. Formatting only works on textual values, not date
values.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Sherry" <Sherry@.discussions.microsoft.com> wrote in message
news:99360DE6-995D-45B2-80E0-744A800DE396@.microsoft.com...
>I need to convert the date format with an output of yyyymmdd'
> I have tried the following code but it doesn't produce the desired output
> CONVERT(VARCHAR(8),supp_creation_date , 112 ) ,
> Any suggestions?
> Thanks in advance