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)