Need help on DateTime Conversion in Sql Server 2005

I am facing problem in DateTime converstion. My input is 09/22/2011, I need to convert into 20110922.

I tried the below one, but开发者_开发知识库 failed. Select CONVERT(VARCHAR(8),'09/22/2011' , 112) as DateConv

---

Here you go:

SET DATEFORMAT mdy
SELECT CONVERT(VARCHAR(8), CAST('09/22/2011' as DATETIME) , 112) AS DateConv

If your input is actually a dateTime variable like you said (but didn't show in your code example), you can simplify this down to:

SELECT CONVERT(VARCHAR(8), @myDate  , 112) AS DateConv

---

This will do the trick:

select year('09/22/2011') * 10000 + month('09/22/2011') * 100 + day('09/22/2011')

---

Without any other information, SQL is interpreting the '09/22/2011' as a varchar already, and just passes the data through (ignoring CONVERT's style argument). If you use the following line:

SELECT CONVERT (VARCHAR (8), CAST ('09/22/2011' as DATETIME), 112) as DateConv

it should work as expected since it will then view the '09/22/2011' as a date value. If you were getting the date from a column in a table, it would already know the type and you will not need to worry about the CAST portion of the expression, just use the column name.