Microsoft SQL Server 2008
Microsoft SQL Server 2008

SQL 2008 - Convert string to date

Good morning,

I have a number of string fields that represent dates. I would like to convert them to date fields like so:

From: 25-JAN-13
To: 01/25/2013

Thanks in advance for any help.

LVL 1

ttist25Asked: 2013-01-25

Who is Participating?

LVL 143

Guy Hengel [angelIII / a3]Billing EngineerCommented: 2013-01-25

http://mobile.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don't-be-scared-and-do-it-right-the-first-time.html
You will need a double convert, see article

LVL 6

esolveSoftware Development ManagerCommented: 2013-01-25

SET DATEFORMAT DMY

DECLARE @var NVARCHAR(100)
SET @var = '25-JAN-13'
SELECT CONVERT(varchar, CONVERT(DATETIME, @var, 101), 101)

LVL 1

ttist25Author Commented: 2013-01-25

Thanks for the responses guys.

Angel - I did a brief review of the article you sent. Thank you. I will need to review it again (probably several times) to get it through my thick skull before I try to implement it. :)

Esolve - thanks for your response as well. In your example you use the date from my example (25-JAN-13). I should've been more specific and said I am trying to "batch" convert the dates that are in the format dd-mmm-yy to mm/dd/yyyy (although after reading the article I'm not sure the slash format is what I want to do).

Thanks again for your help and especially the article (teach a man to fish although a fish would taste great right now).

LVL 71

Scott PletcherSenior DBACommented: 2013-01-25

If you're going to store dates in a varchar format, use 'YYYYMMDD'. This format is always interpreted correctly, so you never need any error-prone and time-consuming conversion codes to use it.