How can I get

"2001-07-02 00:00:00.000" to "20010702"

• Edited by Shanky_621MVP, Moderator Friday, August 21, 2015 1:57 PM spelling

Hi kalsubu

Check this

SELECT REPLACE(CONVERT(VARCHAR(10), '2001-07-02 00:00:00.000', 112),'-','')

To add on to Milan's solution, formatting data for display purposes is a task best done in the presentation layer.  In .NET, you can use a format string:

someDateTimeValue.ToString("yyyyMMdd")

One more method

DECLARE @dt AS DATETIME2 ='2001-07-02 00:00:00.000'

SELECT CONVERT(CHAR(15),@dt,112)

DECLARE @dt AS DATETIME2 ='2001-07-02 00:00:00.000'

SELECT CONVERT(VARCHAR(10),@dt,112)

Not sure why did we use CHAR(15) (5 extra char) in the previous response. That's why I changed my mind.