How to convert 180897 to 18/aug/1997 in sql server 2008

Question

5.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have a date of birth field which have values like 180897, 150399 etc and i want to convert them as like 18/aug/1997 , 15/mar/1999 ..

Posted 10-Nov-14 19:46pm

aarif moh shaikh

Add a Solution

2 solutions

Solution 1

First get the values as dates:

SQL

DECLARE @input_string varchar(8)
SET @input_string = '180897' -- here goes your date

DECLARE @new_string varchar(10)
SET @new_string = LEFT(@input_string, 2) + '/' + SUBSTRING(@input_string, 3,2) + '/' + RIGHT(@input_string, 2)

DECLARE @new_date DATETIME

SET @new_date = CONVERT(DATETIME, @new_string, 3) -- 3 represents dd/mm/yy, you can adapt if you change "." to some other separator

- here is the link[^].

Then you re-cast back to string you want:

SQL

SET @new_string = DAY(@new_date) + '/' + LEFT(DATENAME(MONTH,@new_date),3) +'/' + YEAR(@new_date) -- you might need to cast day and year into varchar again.

Slight simplyfication is to save varchar values while parsing instead of creating datetime object and use DATENAME LEFT(MONTH, middle_part_from_above), 3)

If this helps please take time to accept the solution.

Posted 10-Nov-14 20:17pm

Sinisa Hajnal

Comments

aarif moh shaikh 11-Nov-14 4:26am

i need '18/aug/1997' types of output ..
i have more than 61000 records..

King Fisher 11-Nov-14 4:44am

what have you Tried?

Sinisa Hajnal 11-Nov-14 5:59am

What the number of records has to do with anything? Make a function and call it in a select on the table. Or do all that parsing inline, I just broke it into variables for clarity.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

King Fisher · Accepted Answer · 2014-11-10T22:44:00

Solution 2

Try This:

SQL

DECLARE @input varchar(8), @newDate varchar(10);
SET @input  = '180897'
 
SET @newDate   = LEFT(@input  , 2) + '/' + SUBSTRING(@input, 3,2) + '/' + RIGHT(@input, 2)
SELECT convert(nvarchar,convert(datetime, @newDate , 5),106)

Posted 10-Nov-14 22:44pm

King Fisher

v2