Best way to convert 3/17/2006 to 03172006? use case?

Here is what I have that seems kind of verbose using Case When Month < 10 an
d
day < 10 or Month < 10 and day > 10 or Month > 10 and Day < 10 or Month > 10
and Day > 10... Is there a less verbose way to do this?
declare @.d datetime
--set @.d = '10/17/06'
set @.d = '3/17/06'
select case
when datepart(month, @.d) < 10 and datepart(day, @.d) < 10 then
'0' + cast(datepart(month,@.d) as char(1)) + '0' + cast(datepart(day, @.d)
as char(1)) + cast(datepart(year, @.d) as char(4))
when datepart(month, @.d) < 10 and datepart(day, @.d) > 9 then
'0' + cast(datepart(month,@.d) as char(1)) + cast(datepart(day, @.d) as
char(2)) + cast(datepart(year, @.d) as char(4))
when datepart(month, @.d) > 9 and datepart(day, @.d) < 10 then
cast(datepart(month,@.d) as char(2)) + '0' + cast(datepart(day, @.d) as
char(1)) + cast(datepart(year, @.d) as char(4))
when datepart(month, @.d) > 9 and datepart(day, @.d) > 9 then
cast(datepart(month,@.d) as char(2)) + cast(datepart(day, @.d) as char(2))
+ cast(datepart(year, @.d) as char(4))
End
Any suggestions appreciated.
Thanks,
RichHere's your first mistake:

> set @.d = '3/17/06'
Don't use ambiguous datetime formats!!! Are you letting users enter
whatever format they want? If they enter 3/4/06 do you have some way to
know whether they're English or Canadian or American, so you can tell if
they meant March 4th or April 3rd? Aren't you doing any validation at the
client/presentation tier?
Anyway, let's make the obtuse assumption that everyone accessing your
database is American and that your app will always be hosted in an
environment where all tiers and components are in m/d/y. What is wrong
with:
DECLARE @.d SMALLDATETIME;
SET @.d = '3/17/06';
SELECT RIGHT(d,4) + LEFT(d, 4)
FROM (SELECT d = CONVERT(CHAR(8), @.d, 112)) x
You should read these articles:
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.aspfaq.com/2464
http://www.aspfaq.com/2460
A|||Look up the various arguments for the CONVERT function in SQL Server Books
Online. With 112 you should be able to format it the way you want.
Anith|||This is how i got it...
SELECT replace(convert(varchar, getdate(), 101),'/','') as todays_date
Result: 03312006|||Thank you all for your replies. Great suggestions. But now that I have
slowed down a bit, what I actually need to do is to retrieve a data from a
datetime field that holds a value like this:
2005-09-20 08:14:00.000
I was rushing when I used my sample date of '3/17/06'. Here is what I
came up with based on the suggestions:
select substring(d,5,2) + right(d,2) + left(d,4) from
(select d = convert(char(8), firstdate, 112) from myTable) t1
which return my desired format of 09202005 for the above sample date
Thank you all for your help.
"Rich" wrote:

> Here is what I have that seems kind of verbose using Case When Month < 10
and
> day < 10 or Month < 10 and day > 10 or Month > 10 and Day < 10 or Month >
10
> and Day > 10... Is there a less verbose way to do this?
> declare @.d datetime
> --set @.d = '10/17/06'
> set @.d = '3/17/06'
> select case
> when datepart(month, @.d) < 10 and datepart(day, @.d) < 10 then
> '0' + cast(datepart(month,@.d) as char(1)) + '0' + cast(datepart(day, @.
d)
> as char(1)) + cast(datepart(year, @.d) as char(4))
> when datepart(month, @.d) < 10 and datepart(day, @.d) > 9 then
> '0' + cast(datepart(month,@.d) as char(1)) + cast(datepart(day, @.d) as
> char(2)) + cast(datepart(year, @.d) as char(4))
> when datepart(month, @.d) > 9 and datepart(day, @.d) < 10 then
> cast(datepart(month,@.d) as char(2)) + '0' + cast(datepart(day, @.d) as
> char(1)) + cast(datepart(year, @.d) as char(4))
> when datepart(month, @.d) > 9 and datepart(day, @.d) > 9 then
> cast(datepart(month,@.d) as char(2)) + cast(datepart(day, @.d) as char(2
))
> + cast(datepart(year, @.d) as char(4))
> End
> Any suggestions appreciated.
> Thanks,
> Rich|||> select substring(d,5,2) + right(d,2) + left(d,4) from
Isn't this the same as
select right(d,4) + left(d,4) from
?|||Yes, of course it is. But that is too easy :) Thank you for helping to
discombobulate my brain. This mess is going to end up in a nasty trigger.
I
should probably post the trigger when I am through with it to see if you guy
s
can tame it a little bit.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Isn't this the same as
> select right(d,4) + left(d,4) from
> ?
>
>