Rearrange String

I have a database where dates are stored in the following format yyyymmdd. I want to write a query where this is broken up and returned in the format mm-dd-yyyy ... Is this possible in SQL server?try this

SELECT CONVERT (datetime,, <DateFIELD> , 110)
FROM <TABLE>

Or

SELECT SUBSTR(<DateFIELD>, 5, 2) + "-" + SUBSTR(<DateFIELD>, 7, 2) + "-" + SUBSTR(<DateFIELD>, 1, 4) AS YOurField
FROM <TABLE>

<DateFIELD> - The name of your date field|||if you want mm-dd-yyyy then

select convert(varchar,getdate(),110)
select substring('20030201',5,2) + '-' + right('20030201',2) + '-' + left('20030201',4)

substitute your db field for getdate() or '20030227'|||Pardon my ignorance, but just for future reference, what is the "110" about?|||CONVERT (data_type[(length)], expression [, style])

it is the date style

0 or 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 or 101 USA mm/dd/yy
2 or 102 ANSI yy.mm.dd
3 or 103 British/French dd/mm/yy
4 or 104 German dd.mm.yy
5 or 105 Italian dd-mm-yy
6 or 106 - dd mon yy
7 or 107 - mon dd, yy
8 or 108 - hh:mm:ss
9 or 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 or 110 USA mm-dd-yy
11 or 111 JAPAN yy/mm/dd
12 or 112 ISO yymmdd
-13 or 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 or 114 - hh:mi:ss:mmm(24h)
20 or 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 or 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)|||Look in bol under convert - it gives all the styles.
They affect converting to and from character format.|||Thanks guys... thats been really helpful... I have one other similar problem that ye might be able to help me with... In the same table I have a field for time and it's stored in the following format:

- 9:20am is stored as 920
- 3:30pm is stored as 1530
- 12:20am is stored as 20
- 12:05 is stored as 5

Do ye have any suggestion as to how I could call this back in the correct format?|||try:

if object_id('tempdb..#Tmp') is not null drop table #Tmp
create table #tmp(dt1 int, tm1 int,dt2 varchar(8), tm2 varchar(4))
insert into #tmp values(20030228,920,'20030228','920')
insert into #tmp values(20030228,1530,'20030228','1530')
insert into #tmp values(20030228,20,'20030228','20')
insert into #tmp values(20030228,5,'20030228','5')

select * From #tmp

select convert(varchar,cast(cast(dt1 as varchar) as datetime),110)
, substring(dt2,5,2) + '-' + right(dt2,2) + '-' + left(dt2,4)
from #tmp

select right(convert(varchar,cast('01-Jan-1753 ' +
left(right('0000' + cast(tm1 as varchar),4),2) +
':' +
right('0000' + cast(tm1 as varchar),2) as datetime)),7)
, right(convert(varchar,cast('01-Jan-1753 ' +
left(right('0000' + tm2,4),2) +
':' +
right('0000' + tm2,2) as datetime)),7)
from #tmp

and in anticipation of your next question...

select convert(varchar,cast(cast(dt1 as varchar) +
' ' +
left(right('0000' + cast(tm1 as varchar),4),2) +
':' +
right('0000' + cast(tm1 as varchar),2) as datetime), 100)
, convert(varchar,cast(substring(dt2,5,2) + '-' + right(dt2,2) + '-' + left(dt2,4) + ' ' +
left(right('0000' + cast(tm1 as varchar),4),2) +
':' +
right('0000' + cast(tm1 as varchar),2) as datetime), 100)
from #tmp|||Is there any easier way of doing this... this way seems very complicated !