SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 sql format
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ntn104
Posting Yak Master

172 Posts

Posted - 02/29/2012 :  13:40:45  Show Profile  Reply with Quote
how to I combine two fields with different data type? for example, field1 = decimal(7,0) and field2(date)

data sample:
- FIELD1 = 1234567
- FIELD2 = 2006-04-01
i want a result for FIELD3 = 1234567060401 (where 060401 is yymmdd)

thanks,

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 02/29/2012 :  13:44:21  Show Profile  Visit tkizer's Homepage  Reply with Quote
You would use CONVERT to get them into varchar (use a style for the date one) and then concatenate them with +. Then you can convert back to a numeric data type.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36797 Posts

Posted - 02/29/2012 :  13:44:41  Show Profile  Visit tkizer's Homepage  Reply with Quote
Or better yet, don't do this at all. Instead format your data in your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/29/2012 :  15:58:56  Show Profile  Reply with Quote

DECLARE @Field1 int, @Field2 datetime
SELECT @Field1 = 1234567, @Field2 = '2006-04-01'
SELECT CONVERT(varchar(25),@Field1) + CONVERT(varchar(6),@Field2,12)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

ntn104
Posting Yak Master

172 Posts

Posted - 02/29/2012 :  16:00:08  Show Profile  Reply with Quote
I think the first option I would use since the data in application was not belong to me to format that...

Thanks,

quote:
Originally posted by tkizer

Or better yet, don't do this at all. Instead format your data in your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/29/2012 :  16:07:35  Show Profile  Reply with Quote
quote:
Originally posted by ntn104

I think the first option I would use since the data in application was not belong to me to format that...

Thanks,

quote:
Originally posted by tkizer

Or better yet, don't do this at all. Instead format your data in your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog




is there amy other purpose to get data in this format other than displaying? are you using the concatenated data for something else?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ntn104
Posting Yak Master

172 Posts

Posted - 03/09/2012 :  13:36:29  Show Profile  Reply with Quote
Just for displaying. Thanks,

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

I think the first option I would use since the data in application was not belong to me to format that...

Thanks,

quote:
Originally posted by tkizer

Or better yet, don't do this at all. Instead format your data in your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog




is there amy other purpose to get data in this format other than displaying? are you using the concatenated data for something else?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/



Go to Top of Page

ntn104
Posting Yak Master

172 Posts

Posted - 03/09/2012 :  13:40:18  Show Profile  Reply with Quote
I used your below convert statement, and it works as the way I wanted to display on screen. Thanks,

quote:
Originally posted by X002548


DECLARE @Field1 int, @Field2 datetime
SELECT @Field1 = 1234567, @Field2 = '2006-04-01'
SELECT CONVERT(varchar(25),@Field1) + CONVERT(varchar(6),@Field2,12)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/09/2012 :  21:41:40  Show Profile  Reply with Quote
quote:
Originally posted by ntn104

Just for displaying. Thanks,

quote:
Originally posted by visakh16

quote:
Originally posted by ntn104

I think the first option I would use since the data in application was not belong to me to format that...

Thanks,

quote:
Originally posted by tkizer

Or better yet, don't do this at all. Instead format your data in your application.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog




is there amy other purpose to get data in this format other than displaying? are you using the concatenated data for something else?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/






then why not do it at front end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000