How to get yy year in SQL Server

Question

select Year(Creation_Date) from Asset_Creation where Creation_Date = @Creation_Date 

I fulfill this request when I get the year as 2013 when today's date is delivered. I want the request returned only 13 from 2013. How can i achieve this?

Answer 1

Try

 SELECT RIGHT(YEAR(Creation_Date), 2) YY FROM Asset_Creation WHERE ... 

Output Example:

  |  Yy |
 ------
 |  10 |
 |  11 |
 |  13 |

Here is the SQLFiddle demo

Answer 2

For SQL Server 2012:

 SELECT FORMAT(@Creation_Date, 'yy') 

Answer 3

Given that you are storing the 2100 issue using only two digits, I assume that you only need a code that works between 2000 and 2099. In this case, simply subtract 2000:

 select Year(Creation_Date) - 2000 from Asset_Creation where Creation_Date = @Creation_Date 

Answer 4

 select SUBSTRING(Year(Creation_Date), 2, 2) from Asset_Creation where Creation_Date = @Creation_Date 

Answer 5

What about:

 SUBSTRING(YEAR(Creation_Date), 3, 2) 

Answer 6

Here you can use a very useful feature.

name it like that

 SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY') 

function

 CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32)) RETURNS VARCHAR(32) AS BEGIN DECLARE @StringDate VARCHAR(32) SET @StringDate = @FormatMask IF (CHARINDEX ('YYYY',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'YYYY', DATENAME(YY, @Datetime)) IF (CHARINDEX ('YY',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'YY', RIGHT(DATENAME(YY, @Datetime),2)) IF (CHARINDEX ('Month',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'Month', DATENAME(MM, @Datetime)) IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0) SET @StringDate = REPLACE(@StringDate, 'MON', LEFT(UPPER(DATENAME(MM, @Datetime)),3)) IF (CHARINDEX ('Mon',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'Mon', LEFT(DATENAME(MM, @Datetime),3)) IF (CHARINDEX ('MM',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'MM', RIGHT('0′+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2)) IF (CHARINDEX ('M',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'M', CONVERT(VARCHAR,DATEPART(MM, @Datetime))) IF (CHARINDEX ('DD',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'DD', RIGHT('0′+DATENAME(DD, @Datetime),2)) IF (CHARINDEX ('D',@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, 'D', DATENAME(DD, @Datetime)) RETURN @StringDate END GO 

Answer 7

In fact, in SQLServer you can use the Convert function format argument set to 1. The syntax is Convert (varchar (10), @YourDate, 1)