Converting numeric date column into date and time in SQL

Question

I'm currently using SQL Server Management Studio 2008 and the database was created a long time ago. The person that created it made Date into a numeric value and also GMT Time meaning that it is 4 hours ahead of the actual time, thus forcing a -4 Hour function somewhere in the query. What I am interested is ONLY the data from the 4 days before the current day after the 4 hour conversion with the correct Date and Time format.

Columns: Date(numeric(17,9), not null) Parms(nvarchar(2000), null) ... etc.

   Right now my query is:
   -----
   SELECT ItemId, DATE,
   SUBSTRING(Parms,1,6) +' Was removed from:' AS RemovalPath,
   [OBJECT] AS [MOVE LIST]
   FROM JnlList
   where UserCode = 'Automation' AND ListType = 'M'
   Order by Date DESC, RemovalPath DESC;
   -----
   1 sample row output for my query out of 385 rows is:
   -----
   ItemId: 9393
   Date: 20130627.180846113
   RemovalPath: V77963 Was Removed From:
   MOVE LIST: M-002411
   -----
   REQUIRED OUTPUT:
   ItemId: 9393
   Date: 2013-06-27
   Time: 2:08:46
   RemovalPath: V77963 Was Removed From:
   MOVE LIST: M-002411
   -----

Can anyone make an alterations to my query to get the required output? That would be greatly appreciated.

Answer 1

Try this:

;WITH CTE AS
(
    SELECT  CONVERT(DATETIME,LEFT([Date],8)+' '+
                             SUBSTRING([Date],10,2)+':'+
                             SUBSTRING([Date],12,2)+':'+
                             SUBSTRING([Date],14,2)+'.'+
                             RIGHT([Date],3)) [Date],
            ItemId,
            Parms,
            [OBJECT]
    FROM (  SELECT  ItemId,
                    CONVERT(VARCHAR(18),[Date]) [Date],
                    Parms,
                    [OBJECT]
            FROM YourTable
            WEHRE UserCode = 'Automation' AND ListType = 'M') A
)
SELECT  ItemId,
        CONVERT(DATE,[Date]) [Date],
        CONVERT(TIME(0),[Date]) [Time],
        SUBSTRING(Parms,1,6) +' Was removed from:' RemovalPath,
        [OBJECT] [MOVE LIST]
FROM CTE
WHERE [Date] >= CONVERT(DATE,DATEADD(DAY,-4,GETDATE()))