SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Convert MMDDYY to YYYYMMDD


Convert MMDDYY to YYYYMMDD

Author
Message
Express12
Express12
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4104 Visits: 1082
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
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70006 Visits: 40923
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
Dave Ballantyne
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6978 Visits: 8370
Try this link


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



Clear Sky SQL
My Blog
kramaswamy
kramaswamy
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3795 Visits: 1825
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.
kevriley
kevriley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4250 Visits: 2635
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
WilliamBendall
SSChasing Mays
SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)SSChasing Mays (647 reputation)

Group: General Forum Members
Points: 647 Visits: 308
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
timbw
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1162 Visits: 426
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
Express12
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4104 Visits: 1082
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
kevriley
kevriley
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4250 Visits: 2635
simply reverse the process...


declare @yyyymmdd char(8)
set @yyyymmdd = '20070918'
select replace(convert(varchar,convert(datetime,@yyyymmdd,10), 10),'-','')



gives

091807



Kev
Express12
Express12
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4104 Visits: 1082
thx Kev - that worked just fine!

BT
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search