Get DDMMYYYY format in SQL Server without periods or hyphens-CodePudding

I have a little task to do and I need to get ddmmyyyy format for my DateTime values in SQL Server.

I tried to do it with this code

SELECT convert(varchar, getdate(), 104)

and it works fine, but it returns dd.mm.yyyy or 01.11.2021. I need it to be 01112021 but I can't achieve this yet.

I am using this link for getting datetime format codes but I can't find any code that really helps me achieve my goal.

CodePudding user response：

There's no exact match for the required format you need - you can either use

SELECT REPLACE(CONVERT(varchar(20), SYSDATETIME(), 104), '.', '')

(use what you had and just strip out the . after first step of formatting)

or you can use FORMAT which is available in SQL Server 2012 and newer:

SELECT FORMAT(SYSDATETIME(), 'ddMMyyyy')

CodePudding user response：

There is no defined format that fulfills your request. However, you can use the 'Replace' function to strip your value from date separators.

select replace(convert(varchar, getdate(),104),'.','')

OR use the newer FORMAT function (SQL server 2012 )

 SELECT FORMAT(getdate(), 'ddMMyyyy')

CodePudding user response：

You can use replace function to complete it.

select replace(convert(varchar, getdate(), 104),'.','')