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.

Is this question any different from this one: http://stackoverflow.com/questions/17368654/altering-data-display-in-sql-query? — Lamak, Jun 28 '13 at 16:42
@Lamak Yes, I have fixed the column problem so I wanted to delete the previous one and post a new question only concerned with the Date. — omar K, Jun 28 '13 at 16:44

Lamak · Accepted Answer · 2013-06-28T17:29:18.080

5

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()))

edited Jun 28 '13 at 17:29

answered Jun 28 '13 at 16:58

Lamak

69,480
12
108
116

Msg 102, Level 15, State 1, Line 12 Incorrect syntax near 'A'. – omar K Jun 28 '13 at 17:04
Msg 102, Level 15, State 1, Line 12 Incorrect syntax near ')'. – omar K Jun 28 '13 at 17:06
1

@omarK Yes, just updated it. But, really, this are pretty easy to spot, you should try modifying the code just a little – Lamak Jun 28 '13 at 17:07
Msg 207, Level 16, State 1, Line 14 Invalid column name 'ItemId'. Msg 207, Level 16, State 1, Line 17 Invalid column name 'Parms'. Msg 207, Level 16, State 1, Line 18 Invalid column name 'OBJECT'. – omar K Jun 28 '13 at 17:07
I'm not sure why its saying these columns are invalid. – omar K Jun 28 '13 at 17:09
it does not but you removed the UserCode requirement from the query and when I inserted it back in, it gave me an error. – omar K Jun 28 '13 at 17:12
and also can you explain the significance behind "with cte as" at the beginning? What does that do? – omar K Jun 28 '13 at 17:15
@omarK Ok, added those filters to the answer. I'm using a "Common Table Expression" instead of a derived table to make the code more readable. Here is a link to the documentation: http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx – Lamak Jun 28 '13 at 17:17
Actually one last concern, is it possible for you to remove all the microsoft seconds in the Time? Instead of 18:08:46:1130000 it should be displaying 18:08:46. – omar K Jun 28 '13 at 17:28
1

@omarK Ok, done. (By the way, those are not *microsoft* seconds) – Lamak Jun 28 '13 at 17:29
I meant to say micro seconds*. I'm not sure why their still appearing even with your new code. – omar K Jun 28 '13 at 17:33
I meant to say micro seconds*. – omar K Jun 28 '13 at 17:38
@omarK Not sure either. Are you really using `CONVERT(TIME(0),[Date])`? – Lamak Jun 28 '13 at 17:39
I'm using the code that you put up there ^ after you edited. It's just annoying how this person made date a numeric value on the entire database making it difficult in making any clear representation of date and time to the end user. – omar K Jun 28 '13 at 17:42
@omarK Weird, it should show what you want. Then try `CONVERT(VARCHAR(10),[Date],108)` – Lamak Jun 28 '13 at 17:44
It gives me this error: Msg 8115, Level 16, State 5, Line 1 Arithmetic overflow error converting numeric to data type varchar. – omar K Jun 28 '13 at 17:59
@omarK What?, are you using that code on the **last** `SELECT`?, there is no `NUMERIC` column there – Lamak Jun 28 '13 at 18:43
Hi Lamak, the microseconds have been trimmed. However I have just noticed that the time has not been adjusted 4 hours behind in the output. – omar K Jul 02 '13 at 15:08
3

@omarK please look into [`DATEADD`](http://msdn.microsoft.com/en-us/library/ms186819(v=sql.100).aspx). The answer solves the crux of the problem; surely you can adjust the 4 hours yourself? – Aaron Bertrand Jul 02 '13 at 15:18
@omarK you should also look up [scope creep](https://en.wikipedia.org/wiki/Scope_creep). – Aaron Bertrand Jul 02 '13 at 15:19
@AaronBertrand Sorry, I'm still a learning programmer and I wasn't sure how the syntax would look like. – omar K Jul 02 '13 at 15:19
@omarK that is fine, but sometimes it is best to learn by reading the documentation, not by repeatedly asking someone who has already solved your problem to keep spoon-feeding you just a little bit more. – Aaron Bertrand Jul 02 '13 at 15:20
@Aaronbertrand is it possible for you to show me what would need to be changed in the DATEADD? Or is that too much to ask? – omar K Jul 02 '13 at 15:25
@omarK You need to use `DATEADD(HOUR,-4,Column)` on the last `SELECT` on the columns `[Date]`, `[Time]` and on the `[Date]` column on the `WHERE` – Lamak Jul 02 '13 at 15:25
@omarK on its own it wouldn't be, but look at this comment thread. – Aaron Bertrand Jul 02 '13 at 15:25

bastos.sergio · Answer 2 · 2013-06-28T17:15:58.760

0

Something like this should work:

SELECT ItemId,
DATEADD(HH, -4, CONVERT(DATETIME, substring(DATE, 1, 4) + '-' + substring(DATE, 5, 2) + '-' + substring(DATE, 7, 2) + ' ' + substring(DATE, 10, 2) + ':' + substring(DATE, 12, 2) + ':' + substring(DATE, 14, 2) + '.' + substring(DATE, 16, 3))),
SUBSTRING(Parms,1,6) +' Was removed from:' AS RemovalPath,
[OBJECT] AS [MOVE LIST]
FROM JnlList
where UserCode = 'Automation' AND ListType = 'M'
AND DATEADD(HH, -4, CONVERT(DATETIME, substring(DATE, 1, 4) + '-' + substring(DATE, 5, 2) + '-' + substring(DATE, 7, 2) + ' ' + substring(DATE, 10, 2) + ':' + substring(DATE, 12, 2) + ':' + substring(DATE, 14, 2) + '.' + substring(DATE, 16, 3))) > GETDATE() - 4
Order by Date DESC, RemovalPath DESC;

edited Jun 28 '13 at 17:15

answered Jun 28 '13 at 17:08

bastos.sergio

6,684
4
26
36

sergio It gives me this error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ','. – omar K Jun 28 '13 at 17:10
Is it possible for you to remove the 7 microseconds digits in the time part of your code? – omar K Jun 28 '13 at 17:42

Converting numeric date column into date and time in SQL

2 Answers2