add a caracter into the content of a field

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)

add a caracter into the content of a field

Author

Topic

Exir
Posting Yak Master

151 Posts

Posted - 2009-05-30 : 06:23:48

There is a nvarchar field which is saved like this: 19840521
i want it to add some '/' to the content of the field to be shown like this as a result of the select query: 1984/05/21

How can i do this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-30 : 06:28:22

select stuff(stuff(@var, 5, 0, '/'), 8, 0, '/')

KH
[spoiler]Time is always against us[/spoiler]

Exir
Posting Yak Master

151 Posts

Posted - 2009-05-30 : 06:30:56

I got it myself, thanks everybody



select (substring(birth_date,1,2)+'/'+(substring(birth_date,4,2) )+'/'+substring(birth_date,7,4))
 FROM Persons where person_no=817167

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-30 : 06:33:16

quote:
Originally posted by Exir

I got it myself, thanks everybody

select (substring(birth_date,1,2)+'/'+(substring(birth_date,4,2) )+'/'+substring(birth_date,7,4))
FROM Persons where person_no=817167

This wont gove you your expected result

declare @str varchar(8)
set @str='20080302'
select (substring(@str,1,2)+'/'+(substring(@str,4,2) )+'/'+substring(@str,7,4))

Madhivanan

Failing to plan is Planning to fail

Exir
Posting Yak Master

151 Posts

Posted - 2009-05-30 : 06:38:48

Thank you khtan, your query is better than mine :)

Yes Madhivanan you are true, i should change the numbers but the concept is true.

Exir
Posting Yak Master

151 Posts

Posted - 2009-05-30 : 06:45:23

This query gives the answer:

select (substring(birth_date,1,4)+'/'+substring(birth_date,5,2)+'/'+substring(birth_date,7,2))
FROM Persons where person_no=817167

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-30 : 07:53:40

select cast(char(10), convert(datetime, birth_date, 112), 111)
from table1

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-30 : 08:40:10

quote:
Originally posted by Peso

select cast(char(10), convert(datetime, birth_date, 112), 111)
from table1

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

cast should be convert

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-30 : 09:48:07

True

I blame the heat here in South of Sweden right now. 39°C degrees (103°F)...

select convert(char(10), convert(datetime, birth_date, 112), 111)

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-01 : 02:31:09

quote:
Originally posted by Peso

True

I blame the heat here in South of Sweden right now. 39°C degrees (103°F)...

select convert(char(10), convert(datetime, birth_date, 112), 111)

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

Is it?
Here usually it is 40°C+ at the end of May month
So, what would be maximum temperature that you experiece there?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-01 : 02:38:52

What ! 39°C and 40°C+ ? i would probably melted if i were there. Over here, if the temp is above 35°C, we will be hiding indoor.

KH
[spoiler]Time is always against us[/spoiler]

SQLRatankalwa
Starting Member

42 Posts

Posted - 2009-06-01 : 11:40:10

You could also use

LEFT(@var, 4) + '/' + SUBSTRING(@var, 5, 2) + '/' + RIGHT(@var, 2)

Ratan Kalwa
SQL Server Professionals User Group

http://www.revalsys.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources