Convert MMDDYY to YYYYMMDD

Question

Convert MMDDYY to YYYYMMDD

Express12

SSCoach

Points: 18514
More actions
August 17, 2009 at 7:45 am

#140965

Is it posible to convert the following using T-SQL:
MMDDYY to YYYYMMDD
For example, 081709 needs to be rendered as 20090817
thanks in advance..
BT

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

conversions like this are a pain; obviously, whenever possible, this kind of data should be placed in a datatime filed, instead of a varchar/char; if you can, convert your original datasource to save you trouble in the future, but I know from experience that is not always possible.

here's one way to do it:

declare @notARealDate char(6)

Set @notARealDate= '081709'

--arbitrary cuttoff: anything that would be 51-99 is century 1900, else century 2000

SELECT CASE

WHEN CONVERT(INT,RIGHT(@notARealDate,2)) > 50

THEN '19' + RIGHT(@notARealDate,2) + LEFT(@notARealDate,4)

ELSE '20' + RIGHT(@notARealDate,2) + LEFT(@notARealDate,4)

END

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Try this link

http://www.karaszi.com/SQLServer/info_datetime.asp

Clear Sky SQL
My Blog[/url]

kramaswamy SSCoach Points: 18181 More actions · Answer 3

DBASkippack (8/17/2009)
Is it posible to convert the following using T-SQL:
MMDDYY to YYYYMMDD
For example, 081709 needs to be rendered as 20090817
thanks in advance..

Well, I'm not sure if there's a CAST or CONVERT statement that would work for you, but what you could do is something like,

DECLARE @InputDate VARCHAR(8)

SET @InputDate = '081709'

DECLARE @InputYear VARCHAR(4)

DECLARE @InputMonth VARCHAR(2)

DECLARE @InputDay VARCHAR(2)

SET @InputYear = '20' + SUBSTRING(@InputDate, 5, 2)

SET @InputMonth = LEFT(@InputDate, 2)

SET @InputDay = SUBSTRING(@InputDate, 3, 2)

DECLARE @OutputDate SMALLDATETIME

SET @OutputDate = CAST(@InputYear + @InputMonth + @InputDay AS SMALLDATETIME)

PRINT @OutputDate

Course, that's reliant upon this being in the year 2000. If your dates can fall between any number of centuries, then you'll need to use some logic to change the '20' part.

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 4

Another way is to add '-' (hyphens) , which turns the date into a USA format date (style 10)

'081709' becomes '08-17-09'

then convert that to a varchar using ISO datetime format (style 112)

declare @mmddyy char(6)

set @mmddyy = '081709'

select

convert(

varchar,

convert(datetime, substring(@mmddyy,1,2) + '-' + substring(@mmddyy,3,2) + '-' + substring(@mmddyy,5,2),10),

112)

returns

20090817

Kev

WilliamBendall SSCrazy Points: 2591 More actions · Answer 5

I have pretty much the same idea as kevriley

declare @invaliddate varchar(6)

set @invaliddate = '081709'

declare @validdate datetime

select @validdate = convert(datetime, '20'+right(@invaliddate,2) + '-' + left(@invaliddate, 2) + '-' + substring(@invaliddate, 3,2))

print convert(varchar, @validdate, 112)

In this instance you can store the @validdate in a sql table as datetime datatype. But when you retrieve it you display it in the format required.

🙂

----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx

timbw Ten Centuries Points: 1307 More actions · Answer 6

OR on the same thought

turn the date into a USA format date (style 12) YYMMDD

declare @mmddyy char(6)

set @mmddyy = '081709'

select CONVERT(varchar(35),CAST(RIGHT(@mmddyy,2) + LEFT(@mmddyy,4) AS DATETIME),112)

Express12 SSCoach Points: 18514 More actions · Answer 7

Your solution worked perfectly for my last scenario (convertng MMDDYY to YYYYMMDD)

Now I have the inverse scenario.. I need T-SQL to convert the character 8 format "YYYYMMDD" to a character 6 format "MMDDYY"

eg. 20070918 needs to be converted to 091807

(I realize DATE formats are desired but this is part of a filename value that I am dynamically building based on received parameters)

Any assistance is greatly appreciated.

BT

Kev Riley SSCrazy Eights Points: 9075 More actions · Answer 8

simply reverse the process...

declare @yyyymmdd char(8)

set @yyyymmdd = '20070918'

select replace(convert(varchar,convert(datetime,@yyyymmdd,10), 10),'-','')

gives

091807

Kev

Express12 SSCoach Points: 18514 More actions · Answer 9

Express12

SSCoach

Points: 18514

September 18, 2009 at 9:13 am

#1055671

thx Kev - that worked just fine!

BT