How do I get yyyy-mm format from 2 different columns in SQL?-CodePudding

I have 1 column that displays year number in the format 1999 and I have another column that displays month number as 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12.

How do I get the single months to display with a 0 in front? I need to combine these 2 columns to display in the form of yyyy/mm so it will be 1999/01 for January 1999.

I tried:

SELECT
    YearNumber   '/'   FORMAT(MonthNumber, 'mm') AS PaymentMonth

But I get this error:

Conversion failed when converting the varchar value '/' to data type smallint

CodePudding user response：

Please try:

SELECT
    CAST(YearNumber AS varchar(4))   '/'   
    LEFT('0'   CAST(MonthNumber AS varchar(2)), 2) AS PaymentMonth;

CodePudding user response：

Another option, using case when:

The table:

select * from mytable
# YearNumber MonthNumber
#       1999           2
#       2000          11

select YearNumber || '/' ||
  (case when MonthNumber < 10 then '0' else '' end) ||
  MonthNumber as YearMonth
from mytable
# YearMonth
#   1999/02
#   2000/11

Note: the above works in sqlite, which tends to be more permissive with column types. In SQL Server, if the columns are not strings already then you may need to cast(YearNumber as char(4)) or perhaps use the concat function:

select
  concat(YearNumber, '/',
         (case when MonthNumber < 10 then '0' else '' end),
         MonthNumber) as YearMonth
from mytable

Other DBMSes have different dialects, they may differ slightly.

CodePudding user response：

SQL Date Format with the FORMAT function

Use the FORMAT function to format the date and time data types from a date column (date, datetime, datetime2, smalldatetime, datetimeoffset, etc. data type) in a table or a variable such as GETDATE()
To get DD/MM/YYYY use SELECT FORMAT (getdate(), 'dd/MM/yyyy ') as date
To get MM-DD-YY use SELECT FORMAT (getdate(), 'MM-dd-yy') as date.

CodePudding user response：

You can use the concat() function to join them. Depending on the database you can use || instead.

select concat(col1, '/', col2) from tbl;

This is also standard, but not enabled by default on MySQL, and possibly other databases.

select col1 || '/' || col2 from tbl;