Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert MMDDYY to YYYYMMDD Expand / Collapse
Author
Message
Posted Monday, August 17, 2009 7:45 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:22 AM
Points: 656, Visits: 939
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
Post #771903
Posted Monday, August 17, 2009 8:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 8:24 AM
Points: 12,887, Visits: 31,835
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #771941
Posted Monday, August 17, 2009 8:19 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 1:57 AM
Points: 1,949, Visits: 8,304
Try this link


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




Clear Sky SQL
My Blog
Kent user group
Post #771942
Posted Monday, August 17, 2009 8:33 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 7:44 PM
Points: 817, Visits: 1,646
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.
Post #771955
Posted Monday, August 17, 2009 8:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:04 AM
Points: 2,684, Visits: 2,434
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
Post #771960
Posted Monday, August 17, 2009 8:47 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, June 14, 2011 3:01 AM
Points: 95, 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
Post #771970
Posted Monday, August 17, 2009 3:08 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 14, 2013 4:07 PM
Points: 1,122, Visits: 425
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)





Post #772300
Posted Friday, September 18, 2009 7:23 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:22 AM
Points: 656, Visits: 939
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
Post #790362
Posted Friday, September 18, 2009 7:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:04 AM
Points: 2,684, Visits: 2,434
simply reverse the process...

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

gives

091807

Kev
Post #790379
Posted Friday, September 18, 2009 9:13 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 8:22 AM
Points: 656, Visits: 939
thx Kev - that worked just fine!

BT
Post #790464
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse