Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'CONVERT'.

Question

I have this table that I'm trying to convert "2004-06-08 00:00:00.0000000" Datetime to just Date mm/dd/yyyy. Each row has a unique date. This is the code that I used below. This "Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'CONVERT'."

USE taxpayer
SELECT SALESDATE FROM VISION_SALEHIST4,
CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

Answer 1

You have your CONVERT after the FROM clause.

USE taxpayer  
SELECT SALESDATE   
,CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]  
FROM VISION_SALEHIST4  
  

Please see:
https://learn.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15

Answer 2

Hi ChrisSlinko,

Please try below T-SQL;

use taxpayer  
select convert(varchar(10), cast(SALESDATE as date), 101) from VISION_SALEHIST4  

If the response helped, do "Accept Answer" and upvote it.

Best regards,
Cathy

Answer 3

If the data type of the column SALESDATE is datetime or datetime2, you can also use the FORMAT function to convert the date:

USE taxpayer  
SELECT FORMAT(SALESDATE, 'MM/dd/yyyy', 'en-US')  AS [MM/DD/YYYY]  
FROM VISION_SALEHIST4;  

See FORMAT for the details.