Return date as ddmmyyyy in SQL Server

I need the date as ddmmyyyy without any spacers.

How can 开发者_JS百科I do this?

I can get yyyymmdd using CONVERT(VARCHAR, [MyDateTime], 112)

But I need the reverse of this.

SQL Server 2008

CONVERT style 103 is dd/mm/yyyy. Then use the REPLACE function to eliminate the slashes.

SELECT REPLACE(CONVERT(CHAR(10), [MyDateTime], 103), '/', '')

Just for the record, since SQL 2012 you can use FORMAT, as simple as:

SELECT FORMAT(GETDATE(), 'ddMMyyyy')

(op question is specific about SQL 2008)

SELECT REPLACE(CONVERT(VARCHAR(10), THEDATE, 103), '/', '') AS [DDMMYYYY]

As seen here: http://www.sql-server-helper.com/tips/date-formats.aspx

I've been doing it like this for years;

print convert(char,getdate(),103)

I found a way to do it without replacing the slashes

select CONVERT(VARCHAR(10), GETDATE(), 112)

This would return: "YYYYMMDD"

select replace(convert(VARCHAR,getdate(),103),'/','')

select right(convert(VARCHAR,getdate(),112),2) + 
       substring(convert(VARCHAR,getdate(),112),5,2) + 
       left(convert(VARCHAR,getdate(),112),4)

Try this:

SELECT CONVERT(DATE, STUFF(STUFF('01012020', 5, 0, '-'), 3, 0, '-'), 103);

It works with SQL Server 2016.

Try following query to format datetime in sql server

FORMAT (frr.valid_from , 'dd/MM/yyyy hh:mm:ss')