sql format - 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

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

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

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/

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

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/

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/

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/

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources