Concatination Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatination Question

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-02-07 : 05:00:27

Good morning everyone.

I am trying to select a couple of UDF but to make up a date

for eg

select dbo.last_Day_Previous_Month () + '-' + dbo.First_Day_Previous_Month () + '-' + [dbo].[Present_year] ()

could someone point me in the right direction please

Kind Regards

Rob

MCTS / MCITP certified

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-07 : 05:12:00

What should be the format of your output? Your requirements are not making sense...you say that you want to make up a date but "last_Day_Previous_Month" + "First_Day_Previous_Month" + year(getdate()) will for sure not be a date.

- Lumbago
My blog-> www.thefirstsql.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-07 : 05:13:01

What was the error you got?
If the return type of the functions is Integer, you need to convert them to varchar during concatenation

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-02-07 : 05:23:13

i want it to read

dd-mm-yyyy

error i receive is Conversion failed when converting the varchar value '_' to data type int.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 05:32:04

as madhi suggested, convert the returned values of functions to Varchar, it would be somehow like...

convert(varchar(2),dbo.last_Day_Previous_Month ())
or
ltrim(rtrim(str(dbo.last_Day_Previous_Month ())))

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 05:32:54

quote:
Originally posted by MIK_2008

as madhivanan suggested, convert the returned values of functions to Varchar, it would be somehow like...

convert(varchar(2),dbo.last_Day_Previous_Month ())
or
ltrim(rtrim(str(dbo.last_Day_Previous_Month ())))

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-02-07 : 05:37:01

what about when i want to have '-' in between the variables

select dbo.last_Day_Previous_Month () + '-' + dbo.First_Day_Previous_Month () + '-' + [dbo].[Present_year] ()

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-07 : 05:40:25

let them as they are

select convert(varchar(2),dbo.last_Day_Previous_Month ()) + '-' + convert(varchar(2),dbo.First_Day_Previous_Month ()) + '-' + convert(varchar(2),dbo.Present_year ())

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-02-07 : 05:53:22

sorry about this

what about
select '0' + day (dbo.first_Day_Previous_Month ()) + '-' + convert(varchar(3), DATENAME (m,DATEADD (m,-1,getdate()))) + '-' + convert(int,dbo.Present_year ())

i receive Conversion failed when converting the varchar value 'Jan' to data type int.

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-02-07 : 05:57:18

so in the end i want it to read

dd-jan-yyyy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-07 : 07:57:43

Why are you using function? Look at convert function in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-02-07 : 09:04:01

ok thank you very much for your help