Formatting the numerical output

Hi All,

I need to control the output format of a request on "int" columns. For instance, if the "select" returns me a "0" (zero), I would like it to be return with 4 digit = "0000" because this results is then append to some other to built a phone-like number : +33 2121-0000-1544.
In that case, "0" is not equal to "0000"

My ASP request is in attach.

THX.

Fabrice.I found a solution ... Thank's anyway ;)

formatting ints in table

Hi I am storing a customer code in my db that will always be 9 digits long. Sometimes the first digit is zero which gets knocked off when added in.

e.g code 050101111 is stored as 50101111

Can anyone suggest a way of formatting it so it will always be 9 digits long. A similar thing can be done in Excel, anyone have any ideas?

much appreciated

TomUse char(9) as the data type in SQL Server.|||thanks for your help

Tom

Formatting Currency

I'm trying to display currency with ZERO decimal places... the book that I
bought says to use "C" for currency, with formats it traditionally (with 2
decimal places)
the other option was to use D0 (D-zero), which works as far as the decimal
is concerned, but doesn't add the $
should I concatenate a "$" to a amount in DO format? gotta be a better wayuse C0 ... which is the same as D0 with the "$"
"Derek in Richmond" <DerekinRichmond@.discussions.microsoft.com> wrote in
message news:01169023-D1A9-4DF9-A628-EA21A7696539@.microsoft.com...
> I'm trying to display currency with ZERO decimal places... the book that
I
> bought says to use "C" for currency, with formats it traditionally (with 2
> decimal places)
> the other option was to use D0 (D-zero), which works as far as the decimal
> is concerned, but doesn't add the $
> should I concatenate a "$" to a amount in DO format? gotta be a better
way|||Use the format pattern "C0" instead of "C". Note: any digits to the right of
the decimal place will be rounded.
"Derek in Richmond" wrote:
> I'm trying to display currency with ZERO decimal places... the book that I
> bought says to use "C" for currency, with formats it traditionally (with 2
> decimal places)
> the other option was to use D0 (D-zero), which works as far as the decimal
> is concerned, but doesn't add the $
> should I concatenate a "$" to a amount in DO format? gotta be a better way

FORMAT/COVERT DATE 2004-06-22-10.21.36.897641

Hi,
I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
The last 3 digits can be zero. So I need soemthing that can put convert a
DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
Does anybody knos how to do this?
Thanks a lot in advance!
PieterDragu
select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
> The last 3 digits can be zero. So I need soemthing that can put convert a
> DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
> Does anybody knos how to do this?
> Thanks a lot in advance!
> Pieter
>|||It didn't gave me really what I wanted, but thanks anyway, it helped me to
find a solution.
I finally came with this:
CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
as CallDate
Not really a great solution maybe, but it does what I wan't it to do.. :-)
for exemple:
2004-06-22-08.32.05.12300
2004-06-22-08.40.14.59000
2004-06-22-08.41.13.53000
2004-06-22-08.44.38.21700
2004-06-22-08.44.37.73300
2004-06-22-08.45.40.13000
2004-06-22-08.55.36.25000
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hT25FDWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Dragu
> select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
> CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
"2004-06-22-10.21.36.897641".[vbcol=seagreen]
a[vbcol=seagreen]
"yyyy-MM-dd-HH.mm.ss.ttt000".[vbcol=seagreen]
>|||On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:

>It didn't gave me really what I wanted, but thanks anyway, it helped me to
>find a solution.
>I finally came with this:
>CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
>LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
>as CallDate
>Not really a great solution maybe, but it does what I wan't it to do.. :-)
>for exemple:
>2004-06-22-08.32.05.12300
>2004-06-22-08.40.14.59000
>2004-06-22-08.41.13.53000
>2004-06-22-08.44.38.21700
>2004-06-22-08.44.37.73300
>2004-06-22-08.45.40.13000
>2004-06-22-08.55.36.25000
Hi Pieter,
How about this one, then?
SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
' ','-'),':','.') + '000'
2004-06-23-00.02.59.763000
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks! Great solution! :-)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:93bhd0habsbdqi12qtkhpm24kjq8b6flge@.
4ax.com...
> On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
>
to[vbcol=seagreen]
:-)[vbcol=seagreen]
> Hi Pieter,
> How about this one, then?
> SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
> ' ','-'),':','.') + '000'
> 2004-06-23-00.02.59.763000
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

FORMAT/COVERT DATE 2004-06-22-10.21.36.897641

Hi,
I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
The last 3 digits can be zero. So I need soemthing that can put convert a
DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
Does anybody knos how to do this?
Thanks a lot in advance!
Pieter
Dragu
select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
> The last 3 digits can be zero. So I need soemthing that can put convert a
> DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
> Does anybody knos how to do this?
> Thanks a lot in advance!
> Pieter
>
|||It didn't gave me really what I wanted, but thanks anyway, it helped me to
find a solution.
I finally came with this:
CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
as CallDate
Not really a great solution maybe, but it does what I wan't it to do.. :-)
for exemple:
2004-06-22-08.32.05.12300
2004-06-22-08.40.14.59000
2004-06-22-08.41.13.53000
2004-06-22-08.44.38.21700
2004-06-22-08.44.37.73300
2004-06-22-08.45.40.13000
2004-06-22-08.55.36.25000
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hT25FDWEHA.3664@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> Dragu
> select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
> CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
"2004-06-22-10.21.36.897641".[vbcol=seagreen]
a[vbcol=seagreen]
"yyyy-MM-dd-HH.mm.ss.ttt000".
>
|||On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:

>It didn't gave me really what I wanted, but thanks anyway, it helped me to
>find a solution.
>I finally came with this:
>CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
>LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
>as CallDate
>Not really a great solution maybe, but it does what I wan't it to do.. :-)
>for exemple:
>2004-06-22-08.32.05.12300
>2004-06-22-08.40.14.59000
>2004-06-22-08.41.13.53000
>2004-06-22-08.44.38.21700
>2004-06-22-08.44.37.73300
>2004-06-22-08.45.40.13000
>2004-06-22-08.55.36.25000
Hi Pieter,
How about this one, then?
SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
' ','-'),':','.') + '000'
2004-06-23-00.02.59.763000
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks! Great solution! :-)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:93bhd0habsbdqi12qtkhpm24kjq8b6flge@.4ax.com... [vbcol=seagreen]
> On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
to[vbcol=seagreen]
:-)
> Hi Pieter,
> How about this one, then?
> SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
> ' ','-'),':','.') + '000'
> 2004-06-23-00.02.59.763000
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

FORMAT/COVERT DATE 2004-06-22-10.21.36.897641

Hi,
I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
The last 3 digits can be zero. So I need soemthing that can put convert a
DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
Does anybody knos how to do this?
Thanks a lot in advance!
PieterDragu
select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> Hi,
> I need to format my date exactly like this: "2004-06-22-10.21.36.897641".
> The last 3 digits can be zero. So I need soemthing that can put convert a
> DateTime-field to a Custom Format liek this: "yyyy-MM-dd-HH.mm.ss.ttt000".
> Does anybody knos how to do this?
> Thanks a lot in advance!
> Pieter
>|||It didn't gave me really what I wanted, but thanks anyway, it helped me to
find a solution.
I finally came with this:
CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
as CallDate
Not really a great solution maybe, but it does what I wan't it to do.. :-)
for exemple:
2004-06-22-08.32.05.12300
2004-06-22-08.40.14.59000
2004-06-22-08.41.13.53000
2004-06-22-08.44.38.21700
2004-06-22-08.44.37.73300
2004-06-22-08.45.40.13000
2004-06-22-08.55.36.25000
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hT25FDWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Dragu
> select CAST({fn CURRENT_DATE()} AS VARCHAR(10))+'.'+
> CAST({fn extract(hour from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(minute from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(second from getdate())}AS VARCHAR(2))+'.'+
> CAST({fn extract(mi from getdate())}AS VARCHAR(2))+'000'
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O4Tzg9CWEHA.3016@.tk2msftngp13.phx.gbl...
> > Hi,
> >
> > I need to format my date exactly like this:
"2004-06-22-10.21.36.897641".
> > The last 3 digits can be zero. So I need soemthing that can put convert
a
> > DateTime-field to a Custom Format liek this:
"yyyy-MM-dd-HH.mm.ss.ttt000".
> >
> > Does anybody knos how to do this?
> >
> > Thanks a lot in advance!
> >
> > Pieter
> >
> >
>|||On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
>It didn't gave me really what I wanted, but thanks anyway, it helped me to
>find a solution.
>I finally came with this:
>CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
>RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
>LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
>as CallDate
>Not really a great solution maybe, but it does what I wan't it to do.. :-)
>for exemple:
>2004-06-22-08.32.05.12300
>2004-06-22-08.40.14.59000
>2004-06-22-08.41.13.53000
>2004-06-22-08.44.38.21700
>2004-06-22-08.44.37.73300
>2004-06-22-08.45.40.13000
>2004-06-22-08.55.36.25000
Hi Pieter,
How about this one, then?
SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
' ','-'),':','.') + '000'
2004-06-23-00.02.59.763000
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks! Great solution! :-)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:93bhd0habsbdqi12qtkhpm24kjq8b6flge@.4ax.com...
> On Tue, 22 Jun 2004 12:51:31 +0200, DraguVaso wrote:
> >It didn't gave me really what I wanted, but thanks anyway, it helped me
to
> >find a solution.
> >
> >I finally came with this:
> >CONVERT(CHAR(4), DATEPART(yyyy, C.Calldate)) + '-' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(mm, C.Calldate)), 2) + '-' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(dd, C.Calldate)), 2) + '-' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(hh, C.Calldate)), 2) + '.' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(mi, C.Calldate)), 2) + '.' +
> >RIGHT('0' + CONVERT(VARCHAR, DATEPART(ss, C.Calldate)), 2) + '.' +
> >LEFT(CONVERT(VARCHAR, DATEPART(ms, C.Calldate)) + '00000', 5)
> >as CallDate
> >
> >Not really a great solution maybe, but it does what I wan't it to do..
:-)
> >for exemple:
> >2004-06-22-08.32.05.12300
> >2004-06-22-08.40.14.59000
> >2004-06-22-08.41.13.53000
> >2004-06-22-08.44.38.21700
> >2004-06-22-08.44.37.73300
> >2004-06-22-08.45.40.13000
> >2004-06-22-08.55.36.25000
> Hi Pieter,
> How about this one, then?
> SELECT REPLACE(REPLACE(CONVERT(char(23), CURRENT_TIMESTAMP, 121),
> ' ','-'),':','.') + '000'
> 2004-06-23-00.02.59.763000
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)