Converting String to date

I'm trying to substring and contat a field to a date format, problem is the string is not always 8 numbers, sometimes it's 7:

1132006 -> 1/13/2006
12132006 -> 12/13/2006

The field is TTXDA

SELECT     TTXDA AS DATE
FROM         TAXTRANS
ORDER BY DATE DESC

I would like the date in 01/13/2006 format, thanks!!
jcbergmanAsked:
Who is Participating?
 
MageDribbleConnect With a Mentor Commented:
SELECT
CASE len(ttxda)
     WHEN 8 THEN  LEFT(TTXDA, 2) + '/' + SUBSTRING(TTXDA,3,2) + '/' + RIGHT(TTXDA,4)
     WHEN 7 THEN  LEFT(TTXDA, 1) + '/' + SUBSTRING(TTXDA,2,2) + '/' + RIGHT(TTXDA,4)
     ELSE 'Unknown'
END
FROM TAXTRANS.....
0
 
MageDribbleCommented:
WHOOPS didn't see you wanted 1/13/2006 as 01...

Change WHEN 7... to the following:

WHEN 7 THEN  '0' + LEFT(TTXDA, 1) + '/' + SUBSTRING(TTXDA,2,2) + '/' + RIGHT(TTXDA,4)
0
 
wesbirdCommented:
Don't forget the leading zero if it's 7 long

SELECT
CASE LEN(TTXDA)
  WHEN 7 THEN '0' + LEFT(TTXDA,1) + '/' + RIGHT(LEFT(TTXDA,3),2) + '/' + RIGHT(TTXDA,4)
  WHEN 8 THEN LEFT(TTXDA,2) + '/' + RIGHT(LEFT(TTXDA,4),2) + '/' + RIGHT(TTXDA,4)
  ELSE NULL
END
0
 
jcbergmanAuthor Commented:
thanks guys!
0
 
jcbergmanAuthor Commented:
Hrm... when I try and order by it, it doesn't sort the date right.... anyway

How do I property convert this to a date so i can sort by it?

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.