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
>

2005/2005Express SQLOLEDB Provider problem

When creating a connection string in ADO (using Delphi7), I insert the provider like this:

Provider=SQLOLEDB;Data Source=.;Initial Catalog=master;Integrated Security=SSPI;User ID=sa;Password=masterkey

Now, when I try to run this on a SQL Server 2005 or SQL2005 Express database, it won't create the connection
unless I use the SQLNCLI provider.

Is there a way to force them to use the OLEDB provider?
I have MDAC2.8-SP1 installed.Try explicitly adding the server name

Provider=SQLOLEDB;Data Source=<Server Name>;Initial Catalog=master;Integrated Security=SSPI;User ID=sa;Password=masterkey

2005 clr returning xmldocument

I would like to return an xmldocument from a 2005 vb clr stored procedure.

This is my definition for the stored procedure. passing in a string, return xmldoc.

Can I not return an xmldoc as output? The solution will build, but not run.

Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Sub SP_Transform(ByVal cc As String, <Out()> ByVal RetValue As XmlDocument)

Error 1 Column, parameter, or variable #2: Cannot find data type XmlDocument. SqlServerProject1

Interesting question. I'll have to try it.

My gut, however, is that you should just return the XML data using the new Xml Data Type in SQL Server 2005 and just use a DataReader like dr.GetSqlXml(index) in ADO.NET to read the XML and load it into an XmlDocument.

I wrote an example of that here-

Reading XML Data Type into XmlDocument Using ADO.NET - SQL Server 2005 Tutorials

Regards,

Dave