Combine Columns within SQL Query and Export

[wrathyimp]

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

[pritaeas]

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.

[wrathyimp]

Thanks Pritaeas,

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

[pritaeas]

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
)