How to format and concatenate fields in an SQL server view

Posted on 2011-10-04
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
    LVL 25

    Expert Comment

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

    Author Comment

    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

    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

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now