Hi - 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 2008 Forums

Transact-SQL (2008)

Author

Topic

niyaz4872
Starting Member

41 Posts

Posted - 2012-07-28 : 06:54:24

following is my table structure

id(is identity) Date kind
1 2012.28.07 Incoming mail
2 2012.15.07 Internal mail
3 2012.10.07 Issued mail

following is sample results i wan't

documentNumber
Inc/28/1
Int/15/2
Iss/10/3

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-28 : 07:49:06

select
left(kind,3) + '/' + substring(Date,6,2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

I am guessing that Date is a column with data type varchar and not date or datetime otherwise the solution should be

select
left(kind,3) + '/' + right('00' + convert(varchar(2),month(Date)),2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-28 : 12:40:04

quote:
Originally posted by webfred

select
left(kind,3) + '/' + substring(Date,6,2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

I am guessing that Date is a column with data type varchar and not date or datetime otherwise the solution should be

select
left(kind,3) + '/' + right('00' + convert(varchar(2),~~month~~day(Date)),2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

No, you're never too old to Yak'n'Roll if you're too young to die.

shouldnt it be this?

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-07-29 : 09:38:07

quote:
Originally posted by visakh16

quote:
Originally posted by webfred

select
left(kind,3) + '/' + substring(Date,6,2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

I am guessing that Date is a column with data type varchar and not date or datetime otherwise the solution should be

select
left(kind,3) + '/' + right('00' + convert(varchar(2),~~month~~day(Date)),2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

No, you're never too old to Yak'n'Roll if you're too young to die.

shouldnt it be this?

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

Yes you are right.
It is because we germans always have the month in the middle of the date...
Thanks for the correction.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-07-29 : 10:21:53

[code]SELECT LEFT(Kind, 3) + '/' + CONVERT(CHAR(2), [Date], 3) + '/' + CAST(ID AS VARCHAR(11)) AS DocumentNumber
FROM dbo.YourTable[/code]

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-29 : 15:03:11

quote:
Originally posted by webfred

quote:
Originally posted by visakh16

quote:
Originally posted by webfred

select
left(kind,3) + '/' + substring(Date,6,2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

I am guessing that Date is a column with data type varchar and not date or datetime otherwise the solution should be

select
left(kind,3) + '/' + right('00' + convert(varchar(2),~~month~~day(Date)),2) + '/' + convert(varchar(10),id) as documentNumber
from YourTable

No, you're never too old to Yak'n'Roll if you're too young to die.

shouldnt it be this?

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

Yes you are right.
It is because we germans always have the month in the middle of the date...
Thanks for the correction.

No, you're never too old to Yak'n'Roll if you're too young to die.

oh ok

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources