200611 to string 'Nov 2006'

Hi
how to conert (number) 200611 to string 'Nov 2006' ?
thanks
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_com> wrote in message news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>
|||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_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>
|||Hi
select right(convert(char(11),cast( cast( @.number as char(6) ) + '01' as
datetime ),106),8)
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>

200611 to string 'Nov 2006'

Hi
how to conert (number) 200611 to string 'Nov 2006' ?
thanksThis 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), 1
07 ), 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_com> wrote in message news:12lfgcha53al985@.corp.supernews.com...

> Hi
>
> how to conert (number) 200611 to string 'Nov 2006' ?
>
> thanks
>
>|||>> 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.|||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_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>|||Hi
select right(convert(char(11),cast( cast( @.number as char(6) ) + '01' as
datetime ),106),8)
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>

200611 to string 'Nov 2006'

Hi
how to conert (number) 200611 to string 'Nov 2006' ?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_06DA_01C7067D.8CE90AB0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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_com> wrote in message =news:12lfgcha53al985@.corp.supernews.com...
> Hi
> > how to conert (number) 200611 to string 'Nov 2006' ?
> > thanks > >
--=_NextPart_000_06DA_01C7067D.8CE90AB0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

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" =wrote in message news:12lfgcha53al985@.corp.supernews.com...> =Hi> > how to conert (number) 200611 to string 'Nov 2006' ?> => thanks > >

---

--=_NextPart_000_06DA_01C7067D.8CE90AB0--|||>> 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.|||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_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>|||Hi
select right(convert(char(11),cast( cast( @.number as char(6) ) + '01' as
datetime ),106),8)
"klabu" <noone_at_gmail_dot_com> wrote in message
news:12lfgcha53al985@.corp.supernews.com...
> Hi
> how to conert (number) 200611 to string 'Nov 2006' ?
> thanks
>

200611 to string Nov 2006

Hi

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

thanksThis 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:12lfgcha53al985@.corp.supernews.com...

Quote:

Originally Posted by

Hi

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

thanks

>

|||>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.|||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:12lfgcha53al985@.corp.supernews.com...

Quote:

Originally Posted by

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

|||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:12lfgcha53al985@.corp.supernews.com...

Quote:

Originally Posted by

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