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

Question

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

kkbear

SSC-Addicted

Points: 457
More actions
February 4, 2016 at 8:31 am

#324620

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.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

kkbear (2/4/2016)
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.

What is the datatype of the two columns b.Fiscal_Year_End_Month_Nbr and b.Fiscal_Year_End_Day_Nbr?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kkbear SSC-Addicted Points: 457 More actions · Answer 2

They are varchar. And I know how to do just now.

CAST(b.Fiscal_Year_End_Month_Nbr as CHAR(2)) + '/' + CAST(b.Fiscal_Year_End_Day_Nbr as CHAR(2))

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Your columns are not varchar, otherwise you wouldn't have the conversion error.

Also, mm/dd is not a date format valid for SQL Server. Dates need a year to be stored/showed as dates, without a year SQL Server won't be able to treat them as dates, only as strings.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

I agree with Luis, at least one of your columns is INT. Try this:

SELECT *,

CAST(

CAST(x.ThisYear AS char(4)) +

RIGHT('0'+CAST(Fiscal_Year_End_Month_Nbr AS varchar(2)),2) +

RIGHT('0'+CAST(Fiscal_Year_End_Day_Nbr AS varchar(2)),2)

AS datetime)

FROM ( -- sample data

SELECT

Fiscal_Year_End_Month_Nbr = 6,

Fiscal_Year_End_Day_Nbr = 16

) b

CROSS APPLY (SELECT ThisYear = 1900 + DATEDIFF(YEAR,0,GETDATE())) x

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

Chris,

Is there any particular reason for using 1900 + DATEDIFF(YEAR,0,GETDATE()) instead of YEAR(GETDATE()) ?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Luis Cazares (2/4/2016)
Chris,
Is there any particular reason for using 1900 + DATEDIFF(YEAR,0,GETDATE()) instead of YEAR(GETDATE()) ?

No - thanks for pointing this out Luis. I was in the middle of building up the date from 0 ('19000101') and switched because the statement - the one I posted - is simpler to understand.

SELECT *,

[Fiscal Year End] = CAST(

CAST(YEAR(GETDATE()) AS char(4)) +

RIGHT('0'+CAST(Fiscal_Year_End_Month_Nbr AS varchar(2)),2) +

RIGHT('0'+CAST(Fiscal_Year_End_Day_Nbr AS varchar(2)),2)

AS datetime)

FROM ( -- sample data

SELECT

Fiscal_Year_End_Month_Nbr = 6,

Fiscal_Year_End_Day_Nbr = 16

) b

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

Since using character data tends to slow things down, maybe 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)))

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

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".