Reformatting numeric value to zero-filled string

I need to take a numeric field that I need converted to a zero-filled text
value, for example:
123456 convert to 000123456
I've looked at the CONVERT and CASE and haven't come up with a combination
that will perform this function.
Thanks!
Dougtry this as an example:
SELECT ISNULL(RIGHT('000' + CONVERT(VARCHAR, '123456'), 10), '')
yosh
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||select replace(str( 10,10),' ','0')
"Leighton.d" <Leightond@.discussions.microsoft.com> wrote in message
news:464BC261-BE2F-4799-B179-B13C0B1DFAB6@.microsoft.com...
>I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Try,
declare @.num int
set @.num = 123456
select right(replicate('0', 9) + ltrim(@.num), 9)
go
"Leighton.d" wrote:

> I need to take a numeric field that I need converted to a zero-filled text
> value, for example:
> 123456 convert to 000123456
> I've looked at the CONVERT and CASE and haven't come up with a combination
> that will perform this function.
> Thanks!
> Doug|||Wouldn't it be nice if the Access FORMAT$ function were available.
Thanks for all of the input...
Doug|||As a good practice, try the formatting in the client side.
AMB
"Leighton.d" wrote:

> Wouldn't it be nice if the Access FORMAT$ function were available.
> Thanks for all of the input...
> Doug

Reference Excel for date?

I have the following query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME)) AND (DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112)
AS DATETIME)) AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

what I would like to do is reference 2 cells in Excel with dates if I need to run this manually. How would I change the sql to do this. Thanks

DateTime1 = cell(1,1)

DateTime2 = cell(2,1)

Something like this:

Code Snippet

declare @.DateTime1 datetime,

@.DateTime2 datetime

select @.DateTime1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

select @.DateTime2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time

FROM v_AnalogHistory

WHERE (@.DateTime1 >= CAST(CONVERT(VARCHAR(8), GETDATE() - 4, 112) AS DATETIME))

AND (@.DateTime2 < CAST(CONVERT(VARCHAR(8), GETDATE()-3, 112) AS DATETIME))

AND (TagName = N'AIT-500_TOTAL_NH2CL') AND (wwRetrievalMode = N'delta')

AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)

ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

You'll need to ensure that 'Ad Hoc Remote Queries' is enabled on your instance to allow OPENROWSET to function.

|||

Expanding upon Dale's suggestion, if you want to be able to run the code as it is, or on occasion, manually, then it would be better to create a Stored Procedure and call the Stored Procedure. (NOT tested and not asserted to be accurate or runnable -presented for ideas only.)

This procedure can be called in this fashion:

EXECUTE PROCEDURE dbo.MyProcedure

In this case it will attempt to find date values in the Excel File and use them.

If the cells are empty, then the procedure will use the columns DateTime1 and DateTime2.

(From the original posting, I'm assuming that there are such columns...)

EXECUTE PROCEDURE dbo.MyProcedure '2007/06/01', '2007/07/01'

In this case, the procedure will use the dates supplied.

Code Snippet

CREATE PROCEDURE dbo.MyProcedure

( @.InDate1 datetime = NULL,

@.InDate2 datetime = NULL

)

AS

BEGIN

DECLARE

@.ExDate1 datetime,

@.ExDate2 datetime

SELECT @.ExDate1 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A1:A1]')

SELECT @.ExDate2 = F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=c:\Book1.xls;HDR=NO', 'Select * from [Sheet1$A2:A2]')

SELECT TOP 1

DateTime,

TagName,

[Value],

Time = convert( varchar(15), DateTime, 108 )

FROM v_AnalogHistory

WHERE ( coalesce( InDate1, nullif( ExDate1, '' ), DateTime1 ) >=

cast( convert( varchar(8), getdate() -4, 112) AS datetime )

AND coalesce( InDate2, nullif( ExDate2, '' ), DateTime2 ) <

cast( convert( varchar(8), getdate() -3, 112) AS datetime )

AND TagName = N'AIT-500_TOTAL_NH2CL'

AND wwRetrievalMode = N'delta'

AND convert( decimal(38,3), [Value] ) IS NOT NULL

)

ORDER BY

convert( decimal(38,3), [Value] ),

convert( varchar(15), DateTime, 108 )

END

GO