CODEWITHSUNDEEP
sqlsql-servert-sqldatetime-conversion
Daniel Wu
Daniel Wu

Reputation: 6003

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 YYYYMMDD, 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').

Upvotes: 2

Views: 993

Answers (4)

Bitmugger
Bitmugger

Reputation: 11

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

Upvotes: 1

Andriy M
Andriy M

Reputation: 77687

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)

Upvotes: 1

gbn
gbn

Reputation: 432271

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

Upvotes: 4

Gideon
Gideon

Reputation: 18491

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

Upvotes: 2

Related Questions