Combine 2 parts of a filed and place / between? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2005 Forums

Transact-SQL (2005)

Combine 2 parts of a filed and place / between?

Author

Topic

Shanew
Starting Member

20 Posts

Posted - 2009-04-30 : 18:09:53

Hello,

I have an SQL Query that takes to parts of one filed (a date filed) and trys to recombined them with a "/" between them.
This works fine in Access but no luck in SQL.

My Queries
SQL:
SELECT (datepart(year, [SER DT]) + '/' + (datepart(month, [SER DT]))) AS [DOS Year/Month]
From Table

Access:
SELECT (DatePart('yyyy',[SER DT]) & "/" & (DatePart('m',[SER DT]))) AS [DOS Year/Month]
From Table

It errors on + '/' + ....
Any Ides?

Thanks for any help!
Shane

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 18:24:03

The error says: "Conversion failed when converting the varchar value '/' to data type int."

So, it looks like you need to do some casting:

SELECT 
	CAST(datepart(year, [SER DT]) AS VARCHAR(4)) 
	+ '/' 
	+ CAST(datepart(month, [SER DT]) AS VARCHAR(2)) AS [DOS Year/Month]

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-04-30 : 18:26:12

You need to convert your datetime values to a varchar before you try to concatenate them:


SELECT (CONVERT(VARCHAR, datepart(year, [SER DT])) + '/' + CONVERT(VARCHAR,(datepart(month, [SER DT])))) AS [DOS Year/Month]
From Table

Some days you're the dog, and some days you're the fire hydrant.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-04-30 : 18:27:07

Guess I was a bit late...

Some days you're the dog, and some days you're the fire hydrant.

Shanew
Starting Member

20 Posts

Posted - 2009-04-30 : 18:37:28

Lamprey thanks for shearing your knowledge!

Casting is somthing I havent used yet, but now I know.

Thanks for the help, it worked just fine!
Shane

Shane Weddle
www.TechKnowPros.com

Shanew
Starting Member

20 Posts

Posted - 2009-04-30 : 18:49:31

Skorch, Thanks for your help as well!

I tried it with your query "CONVERT(VARCHAR" and it worked just fine!

Guess now I wonder what is best to use? I like the word "CONVERT" better but that only because it makes sense to me. I'm not so sure of CAST; It seams new with .Net and I kind of wonder what’s the different.. Guess I will Google it up.

Thanks for helping!
Shane

Shane Weddle
www.TechKnowPros.com

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-04-30 : 19:08:51

Both work just fine. CONVERT is a SQL Server-only keyword which is a bit more powerful in that it can deal with several more datatypes than CAST. CAST however is a standardized keyword that will work across multiple database platforms.

Some days you're the dog, and some days you're the fire hydrant.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-30 : 19:26:57

One other side note, is that if you CONVERT something to VARCAHR and do not specify a data length SQL defaults to 30:

DECLARE @Foo VARCHAR(100)
SET @Foo = 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ'

SELECT CONVERT(VARCHAR, @Foo)

And to be super nit-picky, I suggest CAST over CONVERT as CONVERT is *generaly* only needed for formatting, which should be done in the presentation layer. Additionally, CAST (like COALESCE) is ANSI compliant. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources