Date Conversion YMMDD to YYYYMMDD

[RJL1]

I have a customer that is goign to be sending us a manuafacured date in the following format

YMMDD

Example 40506

Year = 4
Month = 05
Day = 06

I need to convert this to a YYYYMMDD so 40506 would be 20140506

Any suggestions on how to go from a single digit year to a 4 digit year is appreciated

Thanks
RJL

 

[bborissov]

How do you know that this is 2014?
This year can be 2004 or 1994

Borislav Borissov
VFP9 SP2, SQL Server

 

[RJL1]

Sorry I should have posted that. We are going under the assumption that is current decade since we do not think they have product 10 years old coming to us since it perishable items.

Thanks

 

[TamarGranor]

Just clarifying. By "current decade," I think you mean any of the last 10 years. So, in 2020, if the digit is 9, it means 2019, not 2029, right?

Tamar

 

[RJL1]

Correct

 

[bborissov]

DECLARE @Test varchar(5)
SET @Test = '40506' && If the type is other just cast it to varchar(5)

SELECT CAST('201'+@Test as Date)

Borislav Borissov
VFP9 SP2, SQL Server

 

[RJL1]

Thanks for the response. It turn out the customer had only sent the middle of the manufacturing date. There was a leading alpha character and a trailing digit so this is what I came up with after a couple of hours of playing with it.

DECLARE @mfdate VARCHAR(7) = 'S405061';

SELECT LEFT(REPLACE(DATEPART(yyyy, GETDATE()),'',','),3) + SUBSTRING(@mfdate , 2,1) + SUBSTRING(@mfdate , 3,2) + SUBSTRING(@mfdate , 5,2)

Not sure is the best way but for now it does what I need

Thanks

 

[bborissov]

DECLARE @mfdate VARCHAR(7) = 'S405061';

SELECT SELECT LEFT(DATEPART(year, GETDATE()),3) + SUBSTRING(@mfdate , 2,6)

Borislav Borissov
VFP9 SP2, SQL Server

 

[bborissov]

or

DECLARE @mfdate VARCHAR(7) = 'S405061';

SELECT SELECT LEFT(DATEPART(year, GETDATE()),3) + RIGHT(@mfdate , 6)

Borislav Borissov
VFP9 SP2, SQL Server