Converting a date to string in SQL Server stored procedure results

Question

Background

I am a beginner to SQL. I have written a SQL Server stored procedure that pulls certain data from a table from the previous month and displays the results. The results are then saved and sent to via email as a .csv (this is not part of the stored procedure).

Objective

As per the last line of sequel from the stored procedure SQL, I am trying to ensure that certain column values from the results are converted to strings, however not altering the table the stored procedure is querying. The current values in the columns I'm trying to convert to string are 2020-12-01 09:20:00.000

Issue

When I run the stored procedure, I get an invalid column name error, and as the column trade_date is valid, I'm sure its just my lousy attempt.

This is my stored procedure:

SELECT *
FROM tom..blot_v
WHERE trade_date BETWEEN DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) 
                     AND EOMONTH(GETDATE(), -1)
  AND type NOT IN ('cancel', 'ORDER-FILLED', 'ORDER-UNFILLED') 
  AND trade_type = 'QR'
ORDER BY trade_date

SELECT CONVERT(varchar, trade_date, 20)

Help

Is anyone able to advise where I'm going wrong with my code, specifically the

SELECT CONVERT(varchar, trade_date, 20)

which was my attempt to try converting the output of the trade_date value, before attempting for others.

question from:https://stackoverflow.com/questions/65945155/converting-a-date-to-string-in-sql-server-stored-procedure-results

Answer 1

Barring whether you should or not, based on the code you have presented, the reason you are getting column errors is that even though you may think that because you ran the query above it, that the field is available, it is not.

If you wanted to convert a field to text, you should do it while the query is running and there is access to the data. So this means:

SELECT CONVERT(varchar, trade_date, 20) as tradedate, * 
FROM tom..blot_v
WHERE trade_date BETWEEN DATEADD(DAY, 1, EOMONTH(GETDATE(), -2)) 
                     AND EOMONTH(GETDATE(), -1)
  AND type NOT IN ('cancel', 'ORDER-FILLED', 'ORDER-UNFILLED') 
  AND trade_type = 'QR'
ORDER BY trade_date