change date format

I have a table with a date field. I need to convert the date so that it
returns in a text format of DDMMYY - no separators.
How do I do this?
I have done it like as follows: Is there a better way of doing it?
Here is what I have:
right(RTrim(convert(char(8),TrnDate,12)),2) +
substring(convert(char(8),TrnDate,12),3,2) +
left(convert(char(8),TrnDate,12),2) as DteVal
"Newbie" <noidea@.nospam.com> wrote in message
news:%23gfQUJ0zEHA.1192@.tk2msftngp13.phx.gbl...
> I have a table with a date field. I need to convert the date so that it
> returns in a text format of DDMMYY - no separators.
> How do I do this?
>
|||On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:

>I have done it like as follows: Is there a better way of doing it?
Hi Newbie,
Have you tried
REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for the reply. I gave it a go but for the date 17/04/2000 it
returned
170420 instead of
170400
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com...
> On Sat, 20 Nov 2004 21:57:21 -0000, Newbie wrote:
>
> Hi Newbie,
> Have you tried
> REPLACE (CONVERT(char(8), TrnDate, 103), '/', '')
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo's solution needs format 3, not 103:
This should work:
REPLACE (CONVERT(char(8), TrnDate, 3), '/', '')
Steve Kass
Drew University
Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
>
>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
>news:55ivp01419rdl6enh9v0lolt1cak6a5r14@.4ax.com.. .
>
>
>
|||On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:

>Thanks for the reply. I gave it a go but for the date 17/04/2000 it
>returned
>170420 instead of
>170400
Hi Newbie,
My bad. Halfway through my post, I forgot that you didn't want all four
digits of the year and I autopiloted back to a format that uses the
complete year.
Steve is correct: 103 should have been 3.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks - works a treat!
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:i342q0dcnf10r39nn5m35q4u38cblgtiib@.4ax.com...
> On Sun, 21 Nov 2004 09:37:38 -0000, Newbie wrote:
>
> Hi Newbie,
> My bad. Halfway through my post, I forgot that you didn't want all four
> digits of the year and I autopiloted back to a format that uses the
> complete year.
> Steve is correct: 103 should have been 3.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)