CODEWITHSUNDEEP
sql-servert-sqldatetimeauto-increment
dennis schütz
dennis schütz

Reputation: 383

Create column with Year and autoincrement

i want to know if is possible to create a table with a Column which got a autoincrement with the number of the year in front if ( Format: YYYY## ): 

CRATE TABLE "Example"(
[Number] autoincrement(YYYY##),
[Text] nvarchar(30)
)

how has the column 'number' to look like? ist it even possible?

thanks!

Part 2:

The number behind the year should start from 0 after every year.

Example output:

2013-01

2013-02

2013-03

2014-01

Upvotes: 2

Views: 4854

Answers (2)

scubaFun
scubaFun

Reputation: 1289

As far as I know, you cannot do this with the autoincrement column. You could, instead, have an additional column with the day of creation of the record. Then, when you want to output the column with the autoincrement, you can format the output according to your especifications.

i.e.

CREATE TABLE "Example" (
[Number] autoincrement(YYYY##),
[Text] nvarchar(30),
[CreatedDate] datetime
) 

-- output
select convert(varchar(4), year(CreatedDate)) + convert(varchar, Number)

Hope this helps

Upvotes: 1

valex
valex

Reputation: 24144

You can use Computed Columns

For example:

 CREATE TABLE T 
    (
        ID int IDENTITY (1,1) NOT NULL, 
        DateField datetime DEFAULT (getdate()),
        DateIDChar AS CAST(YEAR(DateField) AS Varchar)+'-'+CAST(ID as VARCHAR),
        DateIDInt AS YEAR(DateField) * 10000+ID,
        SampleField varchar(100)
    );

SQLFiddle demo

Here:

  • ID is a base autoincremented field
  • DateField is a DateTime part with current date as default
  • DateIDChar - example computed field formatted as a varchar
  • DateIDId - example computed field formatted as a int

Upvotes: 5

Related Questions