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"
