Convert string in T-SQL (YYYMMDD)

I have a column exit_date with varchar like 5/21/2008 0:00 and I need to update it to a string like Y开发者_JAVA技巧YYYMMDD, any way to do that?

5/21/2008 0:00  ==> 20080521  
1/1/2007 0:00   ==> 20070101

How to do something like

select convert('5/21/2008 0:00', 'YYYYMMDD').

CONVERT allows a style for conversions datetime/varchar in both directions. Saying that, you have a format that is not listed. And you actually have 2 conversions too: you need to get it into datetime first

In my local SQL install that has the default "us_english" settings, this works out of the box

select convert(datetime, '5/21/2008 0:00')

thus

select convert(char(8), convert(datetime, '5/21/2008 0:00'), 112)

You can use SET LANGUAGE to modify to us_english temporarily

Head over to http://www.sql-server-helper.com/tips/date-formats.aspx

I didn't see first that it was a varchar column that needed the conversion.

So, as I said in my comment to Gidon's answer, basically you should probably go like this: CONVERT(varchar(8), CAST(your_varchar_date AS datetime), 112).

If you are converting the values in-place, then here's a fuller example of how to apply it:

UPDATE your_table
SET exit_date = CONVERT(varchar(8), CONVERT(datetime, exit_date), 112)

I just posted a function on my blog to support date conversions in T-SQL using .Net style format masks. Not trying to plug my blog or anything it's just where I post my code snippets.

Using my function SELECT dbo.fnc_FormatDate(getdate(), 'YYYYMMDD') will do what you want.

http://bitmugger.blogspot.com/2011/07/convert-t-sql-datetime-to-strings-using.html