200611 to string 'Nov 2006'

[klabu]

Hi

how to conert (number) 200611 to string 'Nov 2006' ?

thanks

[Arnie Rowland]

This is 'awkward' -but it works. Maybe someone else will have a better idea.

First, recognize that '200611' is not immediately understandable as a valid date datatype.

SELECT left( convert( varchar(15), cast( ( '200611' + '01' ) AS datetime), 107 ), 3 ) + ' ' + left( '200611', 4 )

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"klabu" <noone_at_gmail_dot_comwrote in message news:12*************@corp.supernews.com...

Hi

how to conert (number) 200611 to string 'Nov 2006' ?

thanks

[--CELKO--]

>how to convert (number) 200611 to string 'Nov 2006' ? <<

Some SQLs allow a YEAR TO MONTH data type; SQL Server is not one of
them. This is a shorthand for the range '2006-11-01 00:00:00' to
2006-11-31 23:59:59.99999' in Standard SQL. In this dialect, you will
need to build an auxiliary look up table to get those pairs.

[Tony Rogerson]

declare @number int
set @number = 200611

select left( datename( month, converted_to_date ), 3 ) + ' ' + datename(
year, converted_to_date )
from ( select cast( cast( @number as char(6) ) + '01' as datetime ) as
converted_to_date ) as q

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials
"klabu" <noone_at_gmail_dot_comwrote in message
news:12*************@corp.supernews.com...

Hi

how to conert (number) 200611 to string 'Nov 2006' ?

thanks

[Uri Dimant]

Hi

select right(convert(char(11),cast( cast( @number as char(6) ) + '01' as
datetime ),106),8)

"klabu" <noone_at_gmail_dot_comwrote in message
news:12*************@corp.supernews.com...

Hi

how to conert (number) 200611 to string 'Nov 2006' ?

thanks