Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How to format and concatenate fields in an SQL server view

Posted on 2011-10-04
Medium Priority
Last Modified: 2012-05-12
I am creating a view in an SQL server database and need to combine two fields to create a new field.  The two fields are AccountingPeriod which is a a smallint field which can contain the values 0 through 12 and AccountingYear which is a smallint field containing the four digit year.
I want to use these fields to create a new char(4) field which is the period and the last two digits of the year (ex. AccountingPeriod 3 and AccountingYear 2011 would become "0311")
What is the syntax that I would use to create this field
Question by:dsoderstrom
  • 2
LVL 25

Expert Comment

by:Lee Savidge
ID: 36909954
select '0' + right(cast(isnull(AccountingPeriod, 0) as nvarchar(2)), 2) + right(cast(AccountingYear as nvarchar(4)), 2) as periodyear

Author Comment

ID: 36910545
I had to remove the "select" at the beginning of your code and the "as periodyear" at the end in order for my view to work.  So I end up with the following:

'0' + RIGHT (CAST(ISNULL(dbo.FS_GLBatchHeader.AccountingPeriod, 0) AS nvarchar(2)), 2) + RIGHT (CAST(dbo.FS_GLBatchHeader.AccountingYear AS nvarchar(4)), 2)

This works except for when the AccountingPeriod is a 2 charactor month.  For example, if the AccountingPeriod is 12 and the year is 2010, I get "01210".  I need it to be "1210".
 What do I need to change?
LVL 25

Accepted Solution

Lee Savidge earned 2000 total points
ID: 36910794
Well, yes you would have to remove the select and column alias as the code I gave was an example to get some output.

To fix the issue in my code:

RIGHT ('0' + CAST(ISNULL(dbo.FS_GLBatchHeader.AccountingPeriod, 0) AS nvarchar(2)), 2) + RIGHT (CAST(dbo.FS_GLBatchHeader.AccountingYear AS nvarchar(4)), 2)

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question