Date Format for a String

Using SQL Server 2000 in a view, if the data is Char and six
characters like: 081564
How can you convert it to a date that looks like: 08/15/1964 and make
it a date value?
Thanks for any suggestions!
RBollingAssuming mdy dateformat and english language settings...
DECLARE @.foo VARCHAR(32);
SET @.foo = '081564';
SET @.foo = CONVERT(SMALLDATETIME, LEFT(@.foo, 2) + '/' + SUBSTRING(@.foo, 3,2)
+ '/' + RIGHT(@.foo, 2));
SELECT @.foo;
Otherwise, you may need more converts in there...
"robboll" <robboll@.hotmail.com> wrote in message
news:1188925487.090517.43360@.d55g2000hsg.googlegroups.com...
> Using SQL Server 2000 in a view, if the data is Char and six
> characters like: 081564
> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
> Thanks for any suggestions!
> RBolling
>|||> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
Above is a contradiction in terms. First, there's no "date" datatype in SQL Server.
Perhaps you mean datetime? If so, datetime doesn't have any format attached to it. It is stored
internally as a number of 0's and 1's. The format that *you* see when you look at it is determined
by the client application. You can use the CONVERT function to convert it to a string with a certain
format and look at that string, but now it isn't datetime anymore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"robboll" <robboll@.hotmail.com> wrote in message
news:1188925487.090517.43360@.d55g2000hsg.googlegroups.com...
> Using SQL Server 2000 in a view, if the data is Char and six
> characters like: 081564
> How can you convert it to a date that looks like: 08/15/1964 and make
> it a date value?
> Thanks for any suggestions!
> RBolling
>