CODEWITHSUNDEEP
sqlsql-server-2008datetime
Biswo
Biswo

Reputation: 351

Convert string to date Format string in sql server

How can I convert format of a date string DDMMYYYY (ex: 31012010) to DD/MM/YY (ex: 31/01/2010) in SQL server 2008?

Upvotes: 1

Views: 8300

Answers (4)

J. Chris Compton
J. Chris Compton

Reputation: 566

It's an old question, but I ended up here in Feb 2018, so...

In SQL Server 2012+ consider Format
- ref https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql (B and D)

For 2008 and above:

declare @d char(8)
 -- string manipulation assumes clean data
set @d = '31012010'

SELECT LEFT(@d, 2) +'/'+ SUBSTRING(@d, 3, 2) +'/'+ RIGHT(@d, 4)  As [with slashes]

String manipulation can be inefficient - make sure you understand the impact.
E.g. you would never put this code in a join clause (may be okay on a reporting server).

Personal opinion: the answer from Mark Byers STUFF(STUFF(@d, 3, 0, '/'), 6, 0, '/')) certainly works, but I think it is less maintainable if you have non-DBAs working in your code. (STUFF ref https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql)

Upvotes: 0

tree
tree

Reputation: 740

This extracts the date from a string that is followed by a space. This took me awhile to figure out so I thought I'd share:

SELECT reference, Convert(date,Left(history,charindex(
    ' ',Left(history,12)) - 1)) as theDate
FROM productinfo
Order BY theDate DESC

Upvotes: 0

jenson-button-event
jenson-button-event

Reputation: 18961

SELECT Convert(varchar(10), CAST(MyColumn AS datetime), 103)

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 839044

Try STUFF:

SELECT STUFF(STUFF(your_string_column, 3, 0, '/'), 6, 0, '/')
FROM ...

Upvotes: 2

Related Questions