2

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').
Cœur
  • 37,241
  • 25
  • 195
  • 267
Daniel Wu
  • 5,853
  • 12
  • 42
  • 93

4 Answers4

4

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

gbn
  • 422,506
  • 82
  • 585
  • 676
  • +1. I must have missed your answer somehow, otherwise I wouldn't have posted mine. (I had probably kept the page open for too long before answering.) Anyway, good point about the language setting. Should have remembered that too. – Andriy M Jan 31 '11 at 06:31
2

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

Gideon
  • 18,251
  • 5
  • 45
  • 64
  • 4
    nice link.. gr8 one... would be better if it included the answer too.. SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] – KoolKabin Jan 30 '11 at 08:51
  • @gidion, thanks for the link ;) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] is what the OP needs ;) – Caspar Kleijne Jan 30 '11 at 08:54
  • @KoolKabin, LOL... With such a giant list of options guessed the answer would be in there. If not, hoped the question asker would be able to infer the answer from the info. – Gideon Jan 30 '11 at 08:57
  • But that's doesn't resolve my problem, how to use select convert('5/21/2008 0:00' XXXXX), to get YYYYMMDD? – Daniel Wu Jan 30 '11 at 10:13
  • The answer is: don't use it that way, it's the wrong syntax. `SELECT CONVERT(VARCHAR(8), CONVERT(DATETIME, '5/21/2008 0:00'), 112)` is what you should use for your particular example. – Andriy M Jan 30 '11 at 10:25
1

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)
Andriy M
  • 76,112
  • 17
  • 94
  • 154
1

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

Taryn
  • 242,637
  • 56
  • 362
  • 405
Bitmugger
  • 11
  • 1