Search code examples
sqldatedatetimessmsdateadd

SQL table column need to change datetime format to date

I have a column in a table with this format '2017-05-09 14:52:32.000' I would appreciate a way to convert it to a date format like MM/DD/YYYY I have tried with: 

select DATEADD(column, DATEDIFF(column, 0, getdate()), 0)
  FROM table

but I get this error:

Column is not a recognized datediff option.

I've also tried another way and failed.

Please advise.

Solution

  • I'm assuming from the error message you reported that you're on SQL Server.

    In SQL Server, the DATEADD and DATEDIFF functions take a "date part" as the first argument: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND or MILLISECOND:

    SELECT DATEADD(DAY, DATEDIFF(DAY, [COLUMN], GETDATE()), [COLUMN]) 
FROM [TABLE]

    might be want what you want.

    However,

    SELECT CONVERT(DATE, [COLUMN]) AS [COLUMNDATE]

    Might be even easier.