How do I concatenate Month and Day to get date mm/dd format

Transact-SQL
1

I want to concatenate month and day in two columns to get date format with mm/dd. So I use below script.

CAST(b.Fiscal_Year_End_Month_Nbr + '/' + b.Fiscal_Year_End_Day_Nbr as datetime) as [Fiscal Year End]

But it returns an error like “Conversion failed when converting the varchar value '/' to data type int”.

How should I do to modify it? Thank you.

2

You can try this one,

DECLARE @Year INT = 2015, @Month INT = 02

SELECT CONVERT(DATE, CAST((@Year*100+@Month) AS VARCHAR(6))+'01') AS [YYYY-MM-DD]

may it help yours.

3

The error is caused by the year and month being integer. They must be converted into a character datatype as @jason_clark shows.
His example relies on the fact SQL Server likes YYYYMMDD as a date, no slash needed.

4

Any use of strings is slower than arithmetic. Therefore, I prefer to stick to date math when possible, like this:

SELECT DATEADD(DAY, b.Fiscal_Year_End_Day_Nbr - 1, 
       DATEADD(MONTH, b.Fiscal_Year_End_Month_Nbr - 1, 
       DATEADD(YEAR, b.Fiscal_Year_End_Year_Nbr - 1900, 0))) AS datetime_result
FROM (
    SELECT 2015 AS Fiscal_Year_End_Year_Nbr, 11 AS Fiscal_Year_End_Month_Nbr, 23 AS Fiscal_Year_End_Day_Nbr UNION ALL
    SELECT 2015, 12, 16
) AS b
1 Like
5

I want to concatenate month and day in two columns to get date format with mm/dd. So I use below script.

NO. You should follow ISO-8601 rules for displaying temporal data (xkcd: ISO 8601) and then read this:

Report Range Tables

A common application is have special reporting periods, perhaps based on the fiscal calendar or business defined events (“Annual Going out Of Business Sale!” was one of my favorites).

CREATE TABLE Report_Periods
(report_name VARCHAR(30) NOT NULL PRIMARY KEY,
report_start_date DATE DEFAULT CURRENT_TIMESTAMP NOT NULL,
report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (report_start_date <= report_end_date),
etc);

These reports can overlap; a fiscal quarter will be contained in the range of its fiscal year. There can be gaps between them; we have to wait a year between each “Annual Going out Of Business Sale!” and there might be long stretches of time without any special sales. But we want to know their ranges so that the table is fairly constant once it is created.

The basic skeleton for use with these tables is

SELECT R.report_name, << summary computations >>
FROM ReportRanges AS R, [Events] AS E
WHERE E.event_date BETWEEN R.report_start_date AND report_end_date
AND R.report_name IN (<>)
GROUP BY R.report_name;

The <> would probably be events nested inside each other, like fiscal quarters inside a fiscal year. While this is a useful and portable programming trick, you need to consider replacing it with the newer OLAP extensions to the GROUP BY clause such as ROLLUP and CUBE.

You have tried to re-invented 1070's COBOL (it uses strings for temporal data) and lost all the advantages of RDBMS and SQL!