Use the CONVERT function: select convert(varchar, dateadd("s",C3,'19700101'),108)
Use one of the following formats: 100 or 0 mon dd yyyy hh:miAM (or PM) 101 mm/dd/yy 102 yy.mm.dd 103 dd/mm/yy 104 dd.mm.yy 105 dd-mm-yy 106 dd mon yy 107 Mon dd, yy 108 hh:mm:ss 109 or 9 mon dd yyyy hh:mi:ss:mmmAM (or PM) 110 mm-dd-yy 111 yy/mm/dd 112 yymmdd 113 or 13 dd mon yyyy hh:mm:ss:mmm(24h) 114 hh:mi:ss:mmm(24h) 120 or 20 yyyy-mm-dd hh:mi:ss(24h) 121 or 21 yyyy-mm-dd hh:mi:ss.mmm(24h) 126 yyyy-mm-ddThh:mm:ss.mmm(no spaces) 130 dd mon yyyy hh:mi:ss:mmmAM 131 dd/mm/yy hh:mi:ss:mmmAM On 11/7/11, Joran, Peter CTR WHS/ITMD/CACI <peter.joran....@whs.mil> wrote: > All > I am in the process of building a view (SQL Server 2008 R2) to use with > Dashboards. The view needs to convert enumerated values to text values and > epoch time (integer) to Date or Date\Time. > The Case function works well and is not an issue. However, the Epoch time > conversion has proven elusive. The Dashboards documentation has an Oracle > example but not one for SQL Server. > > Here is what I've tried that has come close. > C3 is the create date in my test form. > > SELECT DATEADD(s,C3,'19700101') > FROM ARSystem.dbo.T2677 > > 2011-10-20 11:40:14.000 Not bad except I'm unable to drop the seconds and > the AM is missing. > > > SELECT LEFT(DATEADD(s,C3,'19700101'),21) > FROM ARSystem.dbo.T2677 > > Oct 20 2011 11:40AM Close but no cigar. Note that adding LEFT changed the > date format. > > Any idea how to drop the seconds and add the AM\PM in the first query? > > Many thanks! > Pete > > > > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are" > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"