Trying to create a date field to union two tables

Question

I'm trying to pull together two databases in a view that will show invoicing from both systems.

One table lists invoices by date (ie January's sales are any invoice with a January date), the other table lists invoices by year and period (so, January's sales are '2017' period '7' - irrespective of date). As the second system allows invoices to be posted within a period that is from outside that periods date range, I'm trying to create a date field from the year/period fields using a case statement in order to show the same Invoiced sales as the system does.

LedgerYear +
case
when LedgerPeriod = 4 then '-10-01'
when LedgerPeriod = 5 then '-11-01'
when LedgerPeriod = 6 then '-12-01'
when LedgerPeriod = 7 then '-01-01'
when LedgerPeriod = 8 then '-02-01'
when LedgerPeriod = 9 then '-03-01'
when LedgerPeriod = 10 then '-04-01'
when LedgerPeriod = 11 then '-05-01'
when LedgerPeriod = 12 then '-06-01'
when LedgerPeriod = 1 then '-07-01'
when LedgerPeriod = 2 then '-08-01'
when LedgerPeriod = 3 then '-09-01'
end
as 'InvDate',

I'm getting the following..

Error converting data type varchar to numeric.

Presumably, the LedgerYear is numeric and the '-10-01' etc are varchars. The InvDate in the other table in the union is in 'datetime' format.

How can I get the output of the case statement to be in datetime format?

Sample data and desired results are really helpful in explaining a problem. — Gordon Linoff, Jan 06 '17 at 12:34
Possible duplicate of [How to Concatenate Numbers and Strings to Format Numbers in T-SQL?](http://stackoverflow.com/questions/951320/how-to-concatenate-numbers-and-strings-to-format-numbers-in-t-sql) — 3N1GM4, Jan 06 '17 at 12:37

score 3 · Accepted Answer · answered Jan 06 '17 at 12:34

3

I believe you just need a cast:

select (cast(LedgerYear as varchar(255)) +
        case . . .
       ) as InvDate

Notes:

The + operator works on both strings and numbers. When any argument is a number, it is addition. Hence the need for a cast()/convert().
Only use single quotes for string and date constants. Don't use them for column names.
In SQL Server, you can add this as a computed column so you only have to do the calculation once:

alter table t add invDate as (cast(Ledgeryear as varchar(255)) + case . . . );

Then, the column is available in the table.

answered Jan 06 '17 at 12:34

Gordon Linoff

1,242,037
58
646
786

That fixed it - Thanks! – dazzathedrummer Jan 06 '17 at 12:38
You might also want to cast or convert it back to date format after joining the values – Jonathan Porter Jan 06 '17 at 13:25

score 2 · Answer 2 · edited May 23 '17 at 12:33

In addition to the other answers suggesting a CAST or CONVERT, you can also concatenate string and numeric type values with the CONCAT function, which will implicitly convert any non-string type arguments as required (where an implicit conversion is possible):

SELECT
    CONCAT( LedgerYear, 
            CASE LedgerPeriod
                WHEN 1 THEN '-07-01'
                WHEN 2 THEN '-08-01'
                WHEN 3 THEN '-09-01'
                WHEN 4 THEN '-10-01'
                WHEN 5 THEN '-11-01'
                WHEN 6 THEN '-12-01'
                WHEN 7 THEN '-01-01'
                WHEN 8 THEN '-02-01'
                WHEN 9 THEN '-03-01'
                WHEN 10 THEN '-04-01'
                WHEN 11 THEN '-05-01'
                WHEN 12 THEN '-06-01'
            END
          ) AS InvDate,
FROM someTable

I switched up your CASE statement from a searched CASE to simple CASE statement to remove some repetition, you could equally use CHOOSE for further simplification as per Prdp's answer:

SELECT
    CONCAT( LedgerYear, 
            CHOOSE( @LedgerPeriod, '-07-01',
                                   '-08-01',
                                   '-09-01',
                                   '-10-01',
                                   '-11-01',
                                   '-12-01',
                                   '-01-01',
                                   '-02-01',
                                   '-03-01',
                                   '-04-01',
                                   '-05-01',
                                   '-06-01')
          ) AS InvDate,
FROM someTable

However, you can probably achieve what you're trying to do much more easily:

SELECT
CAST(
    CAST(@LedgerYear AS VARCHAR(4)) +
    RIGHT('0' + CAST(CASE WHEN @LedgerPeriod <= 6 
                          THEN @LedgerPeriod + 6 
                          ELSE (@LedgerPeriod + 6) % 12 
                      END AS VARCHAR(2)), 2) +
    '01' 
AS DATETIME) AS columnName
FROM someTable

(and there's probably an even more elegant way to shift your period numbers to month numbers)

This gives you an actual DATETIME field, which you can choose to present any way you like using CONVERT or FORMAT:

DECLARE @LedgerYear INT = 2016
DECLARE @LedgerPeriod INT = 4
DECLARE @LedgerDate DATETIME

SELECT @LedgerDate =
    CAST(
        CAST(@LedgerYear AS VARCHAR(4)) +
        RIGHT('0' + CAST(CASE WHEN @LedgerPeriod <= 6 
                              THEN @LedgerPeriod + 6 
                              ELSE (@LedgerPeriod + 6) % 12 
                          END AS VARCHAR(2)), 2) +
        '01' 
    AS DATETIME) 

SELECT CONVERT(VARCHAR,@LedgerDate,101) -- U.S. (slashes)
SELECT CONVERT(VARCHAR,@LedgerDate,110) -- USA (hyphens)
SELECT CONVERT(VARCHAR,@LedgerDate,103) -- British/French (slashes)

SELECT FORMAT(@LedgerDate, 'd', 'en-US') -- US English
SELECT FORMAT(@LedgerDate, 'D', 'en-US') -- US English verbose
SELECT FORMAT(@LedgerDate, 'd', 'en-GB') -- British English
SELECT FORMAT(@LedgerDate, 'D', 'en-GB') -- British English verbose

SELECT FORMAT(@LedgerDate, 'dd_|_MM_|_yyyy') -- Custom format

Remember that according to MSDN:

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

score 0 · Answer 3 · answered Jan 06 '17 at 12:38

If you are using sql server 2012+ then you can use use CONCAT and Choose function

select concat(LedgerYear , choose(LedgerPeriod,'-07-01',
                                               '-08-01',
                                               '-09-01',
                                               '-10-01',
                                               '-11-01',
                                               '-12-01',
                                               '-01-01',
                                               '-02-01',
                                               '-03-01',
                                               '-04-01',
                                               '-05-01',
                                               '-06-01')) as  'InvDate'

CONCAT does implicit conversion

I've never come across `CHOOSE` before, useful to know, thanks! — 3N1GM4, Jan 06 '17 at 13:12

Trying to create a date field to union two tables

3 Answers3