Date Conversion from Month Date, Year to YYYYMMDD in SQL?

Hi,


This is for MS SQL





I have a pre-existing date in the database which is of the format "Month Date, Year" for eg: "April 11, 2007" , I have around 60 such rows..





I wish to convert all such dates into this format "YYYYMMDD"


for eg: 20070411 through query.....





Could you help me at the earliest with the syntax / query

Date Conversion from Month Date, Year to YYYYMMDD in SQL?
If you look up the convert function you will get it. No need to break it into parts like the previous post.





just this


convert(varchar, field_name, 112)





the varchar is the field type,


field_name is the name of your field


112 represents yyymmdd





http://msdn2.microsoft.com/en-us/library...
Reply:Ok. I got yahoo to accept it by separating some of the functions. When you paste it onto your system, just bring all the lines together to form one line. Be careful you don't delete any commas.





select substring(


convert(char(4),


datepart(yyyy,


convert(smalldatetime,'April 11, 2007'))),1,2) +


convert(char(6),


convert(smalldatetime,'April 11, 2007'),12)





To use a field name instead of a hardcode date, just replace with the field:





select substring(


convert(char(4),


datepart(yyyy,


convert(smalldatetime,


FieldName))),1,2) +


convert(char(6),


convert(smalldatetime,


FieldName),12)


From


TableName





Edit:





Rich is corrrect...





And assuming your date field is of type char you still need to covert it to smalldatetime, prior to using style 112.





select convert(char(10),convert(


smalldatetime,'April 11, 2007'),112)
Reply:what kind of datatype is the field. Is it a datetime or a varchar. I would just select them out and use datepart to rework it maybe.