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 2000 Forums
 Transact-SQL (2000)
 sql format

Author  Topic 

ntn104
Posting Yak Master

175 Posts

Posted - 2012-02-29 : 13:40:45
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

38200 Posts

Posted - 2012-02-29 : 13:44:21
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

38200 Posts

Posted - 2012-02-29 : 13:44:41
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 - 2012-02-29 : 15:58:56

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

175 Posts

Posted - 2012-02-29 : 16:00:08
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

52326 Posts

Posted - 2012-02-29 : 16:07:35
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

175 Posts

Posted - 2012-03-09 : 13:36:29
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

175 Posts

Posted - 2012-03-09 : 13:40:18
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

52326 Posts

Posted - 2012-03-09 : 21:41:40
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
   

- Advertisement -