Date problem in SQL Server

COL1
xxxxxxxxxx&2005xxxxxxxxxxxxxxxxx&&10xxxxxxxx&&&15
xxxxxxxxxxxxxx&2005xxxxxxxxxx&&05xxxxxxxxxxxxxxxxx&&&27
xxxxxxxxx&2005xxx&&19xxxxxxxxxxxxxx&&&05

What SQL syntax is used in a view to display COL1 as:

10/15/2005
05/27/2005
19/05/2005

Thanks for any help!

RBollingerOn 16 Apr 2005 15:35:37 -0700, robboll wrote:

>COL1
>xxxxxxxxxx&2005xxxxxxxxxxxxxxxxx&&10xxxxxxxx&&&15
>xxxxxxxxxxxxxx&2005xxxxxxxxxx&&05xxxxxxxxxxxxxxxxx&&&27
>xxxxxxxxx&2005xxx&&19xxxxxxxxxxxxxx&&&05
>What SQL syntax is used in a view to display COL1 as:
>10/15/2005
>05/27/2005
>19/05/2005
>Thanks for any help!
>RBollinger

Hi RBollinger,

Rather than using a view, I'd prefer to convert the data to a more
sensible format. Do it as a one-time operation for your existing data
and set up some processing for incoming data before storing it.

Anyway, here's the code you can use, either to kludge it as you asked
for or to tidy up this data:

CAST ( SUBSTRING(Col1, PATINDEX('%&[0-9][0-9][0-9][0-9]%', Col1) + 1, 4)
+ SUBSTRING(Col1, PATINDEX('%&&[0-9][0-9]%', Col1) + 1, 2)
+ RIGHT(Col1, 2) AS datetime)

This will create a datetime value. You should store that in the table.
For reports, ormat the date on the front end. Or, if you really must do
it server-side, use CONVERT with an appropriate style parameter (they
are all listed in Books Online).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)