CODEWITHSUNDEEP
sqlsql-serverdatetime
omar K
omar K

Reputation: 245

Converting Numeric date into date time in SQL Server query

I have this query that is running error-free:

Select
    [Object], 
    CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6) END AS [MEDIA]
from JnlDataSection
WHERE [Object] = '061 STATEMENTS'

There is a third column which I am interested in this table called Date. However database was developed a long time ago and the person that did has made date into a (numeric(17,9), not null datatype). E.g. 20130106.050257023.

What I want is this split into two different columns one as date: 2013-01-06 and the second as time: 05:02:57 (no microseconds).

I have come up with this query to convert:

Select
       [Object], 
       CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
       SUBSTRING([Date],10,2)+':'+
       SUBSTRING([Date],12,2)+':'+
       SUBSTRING([Date],14,2)+'.'+
       SUBSTRING([Date],15,3))) AS DATE) 'Date',
       LEFT(CAST(DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
       SUBSTRING([Date],10,2)+':'+
       SUBSTRING([Date],12,2)+':'+
       SUBSTRING([Date],14,2)+'.'+
       SUBSTRING([Date],15,3))) AS TIME),8) 'Time',
       CASE WHEN MsgID = '61' THEN SUBSTRING(Parms,35,6) END AS [MEDIA]
from JnlDataSection
WHERE [Object] = '061 STATEMENTS' 
  AND CAST(substring(convert(varchar(50), [Date]), 0, 5) + '-' +
      substring(convert(varchar(50), [Date]), 5, 2) + '-' +
      substring(convert(varchar(50), [Date]), 7, 2) AS DATETIME) =
      CONVERT(date, DATEADD(day, -1, getdate()))  --Converting

However I receive this error when I run this:

Msg 8116, Level 16, State 1, Line 1
Argument data type numeric is invalid for argument 1 of substring function.

Can anyone modify my query for it to run error free? That would be greatly appreciated.

Upvotes: 0

Views: 2385

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416149

The data is a number type. You have to cast it so a string type before you can use it with the substring function:

substring(cast([date] as varchar(18)), 10,2)

And for that matter, your ultimate goal should be to convert these values into datetime columns.

Upvotes: 2

Related Questions