Gotta keep this in an easy-to-find place...
This one converts SQL Server datetime to the format I use the most (yyyy-mm-dd):
convert(char(10),CREATEDDATE,120) as CREATEDDATE
This one takes seconds from midnight and spits out hh:mm:ss:
convert(varchar(8),Dateadd(s,CREATEDTIME,0),14) as CREATEDTIME
By the way, if you deal with Axapta, the CREATEDDATE and CREATEDTIME fields should be pretty familiar to you. And you've probably encountered this before.