Date 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)

Date Format

Author

Topic

jemacc
Starting Member

42 Posts

Posted - 2006-11-08 : 03:18:51

I am trying to convert a store string date like "200610" to "2006.nov"

This is a string store in the database.

I have this

select name, Substring(Postdate,1,4)+'.'+substring(PostDate,5,4 )as DatePosted from trans

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-08 : 03:34:16

How can '200610' transform into '2006.Nov'? it should be '2006.Oct'

declare @a varchar(10)

Set @a = '200610'

select left(@a, 4) + '.' + left(datename(mm,convert(datetime, @a + '01')),3)

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 03:39:13

[code]UPDATE myTable
SET myColumn = LEFT(CONVERT(varchar, DATEADD(month, 1, myColumn + '01'), 112), 4) + '.' + LEFT(LOWER(DATENAME(month, dateadd(month, 1, myColumn + '01'))), 3)[/code]
Peter Larsson
Helsingborg, Sweden

jemacc
Starting Member

42 Posts

Posted - 2006-11-08 : 06:51:30

quote:
Originally posted by harsh_athalye

How can '200610' transform into '2006.Nov'? it should be '2006.Oct'

declare @a varchar(10)

Set @a = '200610'

select left(@a, 4) + '.' + left(datename(mm,convert(datetime, @a + '01')),3)

Harsh Athalye
India.
"Nothing is Impossible"

Your are right it was my error; it should read 2006.oct.

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-08 : 07:24:00

Dang! I thought it was some kind of incremental update...

UPDATE	myTable
SET	myColumn = LEFT(myColumn, 4) + '.' + LEFT(LOWER(DATENAME(month, myColumn + '01')), 3)

Peter Larsson
Helsingborg, Sweden

Masum7
Starting Member

33 Posts

Posted - 2006-11-08 : 10:17:57

another way to overcome from repeated nested querey:

declare @a as varchar(15)
set @a='200610'
begin
with tempdate(dt) as
(select (convert(varchar(18),convert(datetime,@a+'01'),106)) )
select substring(dt,8,4)+'.'+substring(dt,4,3) from tempdate
end

quote:

Originally posted by Peso

UPDATE	myTable
SET	myColumn = LEFT(CONVERT(varchar, DATEADD(month, 1, myColumn + '01'), 112), 4) + '.' + LEFT(LOWER(DATENAME(month, dateadd(month, 1, myColumn + '01'))), 3)

Peter Larsson
Helsingborg, Sweden

Masum

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-09 : 02:43:22

Masum7,

You answer seems to be for SQL2K5, but it won't work on SQL2000.

Harsh Athalye
India.
"Nothing is Impossible"

Masum7
Starting Member

33 Posts

Posted - 2006-11-10 : 04:39:02

quote:
Originally posted by harsh_athalye

Masum7,

You answer seems to be for SQL2K5, but it won't work on SQL2000.

Harsh Athalye
India.
"Nothing is Impossible"

Thnx for the information..
ya i work only on sql2005

Masum

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-10 : 05:30:08

But OP never mentioned about sql 2005 and unless stated otherwise, I assume its SQL 2000. That's why I raised my concern. No personal offence meant !

Harsh Athalye
India.
"Nothing is Impossible"

jemacc
Starting Member

42 Posts

Posted - 2006-11-10 : 06:29:52

quote:
Originally posted by harsh_athalye

But OP never mentioned about sql 2005 and unless stated otherwise, I assume its SQL 2000. That's why I raised my concern. No personal offence meant !

Harsh Athalye
India.
"Nothing is Impossible"

Thank you all for the suggestion and participation, I went with harsh_athalye solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources