How to convert SQL Server 2016 GETDATE() function output to MMM dd, yyyy?

Question

I am writing a stored procedure where I am getting today's date using GETDATE() and populating the output in a report, where the date format should be like Jan 19,2020 but I am getting the output like 2020-01-19. could someone help me here. I tried many ways but couldn't help.

I have written this code:

  DECLARE @v_current_date date;

  SELECT @v_current_date = CONVERT(varchar, GETDATE(), 107);

Another one, where I am fetching the date from table where the data is stored as yyyy-MM-dd and I want to convert it into like Jan 19,2020

DECLARE @v_updated_date varchar(1000);

SELECT @v_updated_date = last_updated_date 
FROM us_year_expenditure
WHERE tax_id = @v_each_tax_id

Can someone help me out here? Thank you.

Answer 1

If you check the official MS documentation on CONVERT, you'll quickly see that there's no predefined style that matches your requirements.

So the only option left is to use the FORMAT function (and I'd also recommend to use SYSDATETIME() instead of GETDATE()):

DECLARE @v_current_date DATE = SYSDATETIME();

SELECT FORMAT(@v_current_date, 'MMM dd, yyyy');