Formatting in sql query

Hello All,

Is it possible to format an integer to a float value with 2 decimals in an sql query.

If yes, Please help?

SELECT

CONVERT(DECIMAL(4,2),9)

You can replace number 9 above with your integer or integer column.

|||

Thanks Limno,

You are a life saver.

Formatting Date as integer

I want to for that format the date in YYYYMMDD and MMDDYY, with no '-' as data type is integer

I have used the following code (not the conversion function as I don’t need Hyphen '-')

for YYYYDDMM

SET @.DATE = CONVERT(INT,(CONVERT(VARCHAR(4),DATEPART(YYYY,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(MM,GETDATE())) +

CONVERT(VARCHAR(4), DATEPART(DD,GETDATE())) ))

& for MMDDYY

SET @.DATEUS = CONVERT(INT,(CONVERT(VARCHAR(3),DATEPART(MM,GETDATE()))+

CONVERT(VARCHAR(3),DATEPART(DD,GETDATE())) +

SUBSTRING(CONVERT(VARCHAR(4), DATEPART(YY,GETDATE())),3,4) ))

I am getting the result

YYYYMMDD= 200688

MMDDYY =8806

but i want result in

YYYYDDMM = 20060808

MMDDYY = 080806

note: I need to convert in integer, finally, caz database data type is integer.

can any one give me solution

waiting for quick reply

regards,

Anas

Just use convert:

select cast(convert(varchar(8),getdate(),112) as integer)

I use this for our date_dimension/calendar table surrogate keys all of the time.

|||thanx for your quick reply.

formating integer

hello. A question please. I am new on Sqlserver. Could anyone say me how must I format an integer to string. example

I have:

0001 (integer field of 4 positions) and I'd want to see it "0001" (string)

Thanks...

It seems like there is a better way of doing this, but one way is something like:

select right('000'+convert(varchar(4), 1), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0001

|||

Thanks. The problem is that I gave an example for the first field. (0001). But It could be 0010...

|||

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

|||

Waldrop wrote:

What am I missing?

select right('000'+convert(varchar(4), 10), 4) as [4-digit integer]

-- 4-digit integer
--
-- 0010

Nothing. That works fine.

SELECT right('0000' + cast([your_column] as varchar(4)),4)
FROM your_table
|||

I'm NEW !!!! Sorry !!!!. Another question please. I must put your statement in a string var. How can I do the '000' in my string var ?

Thanks...

|||

declare @.myString varchar(4)

set @.myString = right('000'+convert(varchar(4), 7), 4)

set @.myString = right('0000' + cast([your_column] as varchar(4)),4)

set @.myString = '0007'

|||

I'm so sorry. I don't understand. I forgot to say that the statement goes in an store procedure. My problem is how do I put the (') in a string var.

Thanks...

|||

An example of adding quote characters into the string variable:

declare @.myString varchar(6)
declare @.quoteChar char(1)

set @.myString = '''0007''' -- Loads '0007' (including quotes) into @.myString variable
set @.quoteChar = char(39) -- Loads a quote char ' into @.quoteChar variable
set @.quoteChar = '''' -- Loads a quote char ' into @.quoteChar variable

select @.myString as [@.myString],
@.quoteChar as [@.quoteChar]

-- - Output: -

-- @.myString @.quoteChar
-- -
-- '0007' '

declare @.anotherString varchar(4)

set @.anotherString = '0007' -- Loads '0007' (without quotes) into @.anotherString variable
set @.myString = char(39) + @.anotherString + '''' -- concatenates quote characters around '0007'

select @.anotherString as [@.anotherString],
@.myString as [@.myString]

-- - Output: -

-- @.anotherString @.myString
-- --
-- 0007 '0007'

|||Thanks. It works !!!!!

Formating

how to format an integer in sql 2005

for ex: 1.09876 i want to format to 1.1

in access i can use : format(field,'0.0') as field1

thanks

Here You can't format directly.. You can use the following code...

Code Snippet

Select Cast(1.050009 as Numeric(5,1))

--or

Select Convert(Numeric(5,1),1.050009)

|||

Another alternative if you are interested in converting to character data is the STR function:

select str(1.09876,5,1) as Format

/*
Format
1.1
*/