Have you looked at the new FORMAT function in SQL Server 2012?

http://msdn.microsoft.com/en-us/library/hh213505.aspx

You should be able to use something like this:

SELECT FORMAT(YourDateColumn, 'yyyyMMdd')

or whatever you really want to use - basically, the same formatting options as in C# / .NET are available when using FORMAT

You cannot write a function to determine which numeric date string corresponds to which format as the date string could be multiple formats:

For example, 010203 could be:

• Format 2: DDMMYY 1st February 03
• Format 3: MMDDYY 2nd January 03
• Format 101: YYMMDD 3rd February 01
• Format 306: DDHHMM 02:03 of Day 1
• Format 402: HHMMSS 01:02:03
• Format 405: MMMMSS 102 minutes 3 seconds
• Format 610: CCYYMM March 0102
• Format 616: CCYYWW Week 3 of 0102

Similarly 10080102 could be:

• Format 4: DDMMCCYY 10th August 102
• Format 102: CCYYMMDD 2nd January 1008
• Format 305: MMDDHHMM 8th October 01:02
• Format 501: HHMMHHMM Time span from 10:08 to 01:01

If anyone inserts the date in this format "CCYYMMDD" then the value should return only 102 as a default in the frontend. How to write the code in SQL?

You cannot, as I described above it is ambiguous what some values are and they could return multiple formats. Instead you should create another column and store the date format in that when the user inputs the date rather than trying to reconstruct the format code from an (ambiguous) number.

Try this:

CONVERT(VARCHAR(8),GETDATE(),12)

The list of available formats can be found here:

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Assuming your "date" column is not actually a date.

Select convert(varchar(8),cast('12/24/2016' as date),112)

or

Select format(cast('12/24/2016' as date),'yyyyMMdd')

Returns

20161224

you just need to convert yyyy to century yy simple string:

    public static string yyyymmdd2cyymmdd (string yyyymmdd)
	{
		var year = int.Parse(yyyymmdd.Substring(0,4));
		var mmdd = yyyymmdd.Substring(4);
		var century = year / 1900;
		var yy = year % 100;
		return $"{century}{yy}{mmdd}";
	}