Convert datetime to date format

Question

I am working on dates in a query and am seeing some unexpected behavior. I want to convert a datetime to dd-mm-yyyy format but seem to be unable to get the right result. On my Dutch machine

I have the following statement:

DECLARE @datetime DATETIME =  '2014-08-25 00:00:00.000'
SELECT FORMAT (@datetime, 'dd-mm-yyyy')

I expect 25-08-2014

However, I get 25-00-2014

Why is this happening and is there a way to get the correct output?

Answer 1

Use MM not mm, since mm represents minutes, not months.

SELECT FORMAT(@datetime, 'dd-MM-yyyy')

Answer 2

Another simple way to get this is : Replace getdate() with your date column.

select convert(varchar, getdate(), 105)

Output:

21-12-2023