Solved

change date format

Posted on 2014-01-08
3
412 Views
Last Modified: 2014-01-09
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
0
Comment
Question by:qube09
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39766496
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39766535
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
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39767848
Why are you using char to store a date?

:)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question