469,890 Members | 1,586 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

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!

RBollinger

Jul 23 '05 #1
1 969
On 16 Apr 2005 15:35:37 -0700, robboll wrote:
COL1
xxxxxxxxxx&2005xxxxxxxxxxxxxxxxx&&10xxxxxxxx&&& 15
xxxxxxxxxxxxxx&2005xxxxxxxxxx&&05xxxxxxxxxxxxxxxx x&&&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)
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

15 posts views Thread by Simon Brooke | last post: by
7 posts views Thread by What-a-Tool | last post: by
4 posts views Thread by franco | last post: by
9 posts views Thread by ice | last post: by
30 posts views Thread by fniles | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.