Count with last 4 digits which represent Date

I have a question

Can we convert date 20-06-2011 (dd-mm-yyyy) into 0611 using sql (SQL server database) ?

And

Can we count ID below keeping in mind last 4 digits which represent date (last 4开发者_开发问答 digits represent date in the form MMYY) so numbers with last 4 digits 0611 count should be 3

What would be the SQl Query for the count?

ID

AOB2340511
AOB4560511
AOB3500611
AOB4410611
AOB5120611
AOB1250411

EDIT: Corrected DATE data type option.

If your date fields are DATE data types you can do the following.

SELECT RIGHT('0' + CAST(DATEPART(MM, @TheDate) as VARCHAR), 2)
    + RIGHT(CAST(DATEPART(YY, @TheDate) as VARCHAR), 2)

If your date fields are stored as a string type, such as VARCHAR, NVARCHAR, etc, then try the following

select SUBSTRING(TheDate, 4, 2) + SUBSTRING(TheDate, 9, 2)

For the question about count, you can try this

SELECT RIGHT(ID, 4) AS TheDate, Count(*) AS TheCount
FROM MyTable
GROUP BY RIGHT(ID, 4)

If you need a query for the count that also shows the ID value, give this a try

SELECT ID, RIGHT(ID, 4) as MMYY,
    COUNT(*) OVER (PARTITION BY RIGHT(ID, 4)) AS TheGroupCount
FROM MyTable
ORDER BY ID

This gives you "YYMM":

SELECT SUBSTRING(CONVERT(VARCHAR,getdate(),12),1,4)

To get the 2-digit month before the 2-digit year you need to do:

SELECT SUBSTRING(CONVERT(VARCHAR,getdate(),12),3,2) + SUBSTRING(CONVERT(VARCHAR,getdate(),12),1,2)

getdate() is just the current datestamp; substitute that with the date field you want to reformat. CONVERT(...,12) gives you YYMMDD datestamp. See this helpful page for more magic numbers to use as the 3rd parameter: http://sql.dzone.com/news/custom-date-formatting-sql-ser

(BTW, using DATEPART(MM,getdate()) gives a 1-digit month not 2 digits, and DATEPART(YY,getdate()) gives a 4-digit year, not 2 digits. I.e. the DATEPART approach seems doomed)