Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

change date format

Posted on 2014-01-08
3
Medium Priority
?
435 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 70

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 93

Expert Comment

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

:)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

564 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