I suggest you to make a function with code down below.
declare @DateValue as varchar(20)<br />
set @DateValue='10/01/2008'<br />
<br />
select case <br />
when substring(@DateValue,4,2) = 1 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'st'<br />
when substring(@DateValue,4,2) = 2 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'nd'<br />
when substring(@DateValue,4,2) = 3 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'rd' <br />
when substring(@DateValue,4,2) <= 9 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),2,1)+'th'<br />
when substring(@DateValue,4,2) > 9 then<br />
<br />
<br />
case <br />
when substring(@DateValue,5,1) = 1 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'st'<br />
when substring(@DateValue,5,1) = 2 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'nd'<br />
when substring(@DateValue,5,1) = 3 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'rd' <br />
when substring(@DateValue,5,1) <= 9 then<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'th' <br />
when substring(@DateValue,5,1) > 9 then <br />
substring (convert(varchar ,cast(@DateValue as datetime),106),1,2)+'th'<br />
end<br />
<br />
end<br />
<br />
+ substring (convert(varchar ,cast(@DateValue as datetime),106),3,4)+','+<br />
substring (convert(varchar ,cast(@DateValue as datetime),106),7,5)<br />
as DateTimeValue
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
modified on Thursday, September 11, 2008 3:23 PM
|