change date format

I need to change the date format of a char(10) field to char(10) style 110. What is the most efficient way. This is sql 2005. Since the dates are consistent I suppose that I could use substring but is there a better way?

ex.05122008  change to  05-12-2008
qube09Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you would normally need a double conversion ... first from string to date, and then back.
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

the issue is that the format ddmmyyyy is not a "known" style for the simple convert...
so, you have to first "rework" a bi the input string for this.

hence, you could as well "skip" the double conversion, and just do string operations:
update yourtable
  set yourfield = left(yourfield,2) + '-' + substring(yourfield, 3,2) + '-' + right(yourfield,4)
where yourfield like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Open in new window


hope this helps
0
 
Scott PletcherSenior DBACommented:
UPDATE dbo.tablename
SET date_column = STUFF(STUFF(date_column, 3, 0, '-'), 6, 0, '-')


For example:

SELECT date_column, STUFF(STUFF(date_column, 3, 0, '-'), 6, 0, '-')
FROM (
    SELECT '05122008' AS date_column
) AS test_data


Btw, odd format, since of course it won't sort correctly like that.
0
 
Patrick MatthewsCommented:
Why are you using char to store a date?

:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.