Get the 2 Digit Year in T-Sql

Get the 2 digit year in T-SQL

You can do ( YEAR( GETDATE() ) % 100 ) + 1

See GETDATE & YEAR

How to get year in format YY in SQL Server

Try

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

Sample output:

| YY |
------
| 10 |
| 11 |
| 13 |

Here is SQLFiddle demo

Is there a simpler method for getting the two digit year?

Honestly, just don't work with 2 digit years any more; learn the lessons of last century's mistake and use 4 digits.

If you "have" to, then you could use CONVERT with a style code, and then just replace the characters with an empty string:

DECLARE @TestVariable varchar(100) = '1234',
        @Date datetime = GETDATE();

SELECT @TestVariable + REPLACE(CONVERT(varchar(8),@Date, 1),'/','');

T-SQL how to get four digit year value using two digit year part

So based on @Gordon Linoff answer function will look as below:

create function dbo.your_name
(
    @yearYY nvarchar(2)
)
RETURNS nvarchar(4)
as
begin
    declare @yearYYYY nvarchar(4)
    set @yearYYYY = (SELECT ((CASE WHEN @yearYY >= 50 THEN '19' 
                        ELSE '20' END) + @yearYY
                    ) as yyyy)
    return @yearYYYY
end

test:

select dbo.your_name ('70')

How can i select a datetime as ddMMyy, so a two-digit year?

As the docs for CONVERT state, use 103 if you want the century and 3 if you dont:

REPLACE(CONVERT(CHAR(10), ChargeArrival.CreatedAt, 3), '/', '')

Two Digit date format in SQL

For the date part, to get the format you want you, try this:

SELECT 
  RIGHT(REPLICATE('0', 2) + CAST(DATEPART(DD, accid) AS VARCHAR(2)), 2) +
  RIGHT(REPLICATE('0', 2) + CAST(DATEPART(MM, accid) AS VARCHAR(2)), 2) +
  RIGHT(DATEPART(YY, accid), 2) AS CustomFormat
FROM yourtablename
...

The DATEPART(DD, accid) will give you the day part and the same for mm and yy will give you the month and the year parts. Then I added the functions RIGHT(REPLICATE('0', 2) + CAST(... AS VARCHAR(2)), 2) to add the leading zero, instead of 1 it will be 01.

• SQL Fiddle Demo

---

As @bernd-linde suggested, you can use this function to concatenate it with the name part like:

concat(Name, ....) AS ...

Also you can just SELECT or UPDATE depending on what you are looking for.

As in @bernd-linde's fiddle.

what to do get a two digit number by using datepart () in sql?

Try this for MySql or from SQL Server 2012 -

select FORMAT(DATEPART(hour,'1900-01-01 07:45:00.010'),'00')

For SQL Server before 2012 -

select right('0' + DATEPART(hour,'1900-01-01 07:45:00.010'),2)

two digit year as primary key in sql server

One way of doing this, if you can alter the table structure, is to add a persisted computed column for the year part, and then add a primary key for (id, computer_col), like this:

CREATE TABLE myTable (
    id INT NOT NULL, 
    d DATE NOT NULL, 
    y AS DATEPART(YEAR,d) PERSISTED NOT NULL, 
    PRIMARY KEY(id,y)
    )

I'm not saying this is a good solution in any way, but it should work. Using a trigger on insert or a check constraint might be better.

Using your test data this will allow the first insert statement, but disallow the second as it violates the primary key constraint.

---

Related Topics