Hi,

I have a sql view below:

SELECT     b.Stk AS secode, 
           b.TktE AS Ticket, 
           CAST(a.Last / 10 AS integer(10, 3)) AS Last,
           CASE WHEN CONVERT(integer, a.Last) <> 0 THEN (CONVERT(integer, a.Last) - CONVERT(integer, a.Ref)) / 10 ELSE (CONVERT(integer, a.Ref) - CONVERT(integer, a.Prev)) / 10 END AS Change, 
           CAST(a.High / 10 AS integer(10, 3)) AS High,
           CAST(a.Low / 10 AS integer(10, 3)) AS Low,
           CAST(a.Vol AS integer) * 100 AS Vol,
           a.Deal AS Trades, 
           CAST(a.Amt AS integer) AS Value,
           a.Prev,
           a.Ref,
           CAST(a.Bid / 10 AS integer(10, 3)) AS Bid,
           CAST(a.Ask / 10 AS integer(10, 3)) AS Ask, 
           CAST(ASCII(LEFT(a.DatP, 1)) AS Integer) + '1922' AS Year,
           CAST(ASCII(RIGHT(LEFT(a.DatP, 2), 1)) AS Integer) - '48' AS Month
           CAST(ASCII(RIGHT(a.DatP, 1)) AS Integer) - '48' AS Day,

FROM       StkQt AS a INNER JOIN
           StockList AS b ON a.Row = b.Row

I need to merge the last three (year,month,day) columns as Date; within the same query.

And then export to an excel or csv file.

Thanks

You can use CONCAT on the last three columns, if you CAST them to VARCHAR first. You can then CAST it back to a DATE if needed.

Thanks Pritaeas,

Can show me what will be the code, not an SQL expert.

I think this should work, although I cannot test this right now:

CAST(
  CAST(CAST(ASCII(LEFT(a.DatP, 1)) AS Integer) + 1922 AS VARCHAR) + '-' +
  CAST(CAST(ASCII(RIGHT(LEFT(a.DatP, 2), 1)) AS Integer) - 48 AS VARCHAR) + '-' +
  CAST(CAST(ASCII(RIGHT(a.DatP, 1)) AS Integer) - 48 AS VARCHAR) 
  AS DATE) AS MyDate

or:

SELECT 
  *,  
  CAST(
    CAST(Year AS VARCHAR) + '-' +
    CAST(Month AS VARCHAR) + '-' +
    CAST(Day AS VARCHAR) 
  AS DATE) AS MyDate
FROM (
  your query here
)