Change field size

SQL 2005
I have a table with a number of nvarchar fields. One field in the midde was
1000 in length. I ran a query with:
ALTER TableName
ALTER ColumnName nvarchar(2000).
This apeared to increase the field size to 2000 (as seen in Server
Management Studio), but it will still not store any more than 1000 characters.
Have I done it incorrectly.
There appears to be still space in my record as I can add a field at the end
2000 long and that works correctly.
Thanks,
Steve.Why do you say it only stores 1000 characters? Is it because what you are
seeing when you select it? If so then change the max size of the columns
characters returned in the options menu to support more. What does
MAX(DATALEN(YourColumn)) say for that column?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> SQL 2005
> I have a table with a number of nvarchar fields. One field in the midde
> was
> 1000 in length. I ran a query with:
> ALTER TableName
> ALTER ColumnName nvarchar(2000).
> This apeared to increase the field size to 2000 (as seen in Server
> Management Studio), but it will still not store any more than 1000
> characters.
> Have I done it incorrectly.
> There appears to be still space in my record as I can add a field at the
> end
> 2000 long and that works correctly.
> Thanks,
> Steve.
>|||Andrew - many thanks for your reply. That got me thinking and I checked the
(Delplhi) component I am using to link. It had field definitions in it and I
had forgotten to refresh it. Did that and now all is working correctly.
Thanks,
Steve.
"Andrew J. Kelly" wrote:
> Why do you say it only stores 1000 characters? Is it because what you are
> seeing when you select it? If so then change the max size of the columns
> characters returned in the options menu to support more. What does
> MAX(DATALEN(YourColumn)) say for that column?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Sawlmgsj" <Sawlmgsj@.discussions.microsoft.com> wrote in message
> news:1F7CC5B5-47B9-41B4-BD89-14263656BC0E@.microsoft.com...
> > SQL 2005
> >
> > I have a table with a number of nvarchar fields. One field in the midde
> > was
> > 1000 in length. I ran a query with:
> >
> > ALTER TableName
> > ALTER ColumnName nvarchar(2000).
> >
> > This apeared to increase the field size to 2000 (as seen in Server
> > Management Studio), but it will still not store any more than 1000
> > characters.
> >
> > Have I done it incorrectly.
> >
> > There appears to be still space in my record as I can add a field at the
> > end
> > 2000 long and that works correctly.
> >
> > Thanks,
> > Steve.
> >
>

Change dynamically(via code) the SqlDataSource for a GridView....

Hi,

say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2...

Does anybody know how can I alter programmatically these two sqldatasources in a gridview?

Thanks!!!

Ok,I resolved it,

I had to delete the "DataSourceID="SqlDataSource1" from the gridview directive and add the following code to a button event:

GridView1.DataSource = SqlDataSource1

GridView1.DataBind()

|||

But now my new question is :

How can I see what is the current/previous DataSource of my GridView Control?

Thanks...

|||

GridView1.DataSource will return the data source (as an object)

You can also useGridView1.DataSourceID to obtain the ID of the datasource as a string

For example:

dim TheCurrentDataSource as object

TheCurrentDataSource =GridView1.DataSource

GridView1.DataSource = The NextDataSource

GridView1.DataBind()

HTH

change default collation for user database

Hi Folks,
Is there another way to change the default collation for a
user database without using the SQL cmd:
ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
With the ALTER DATABASE it is not possible to change the
database collation whenever there are Check Constraint
which are depending on the database collation.
A go lead for me would also be if someone can tell me in
which master table this is recorded'
Cheers Jack.You have to drop the constraints, then change the database collation, and
recreate the constraints. Don't try to change anything in system table
directly, you can make your database completely unusable.
I have an (unsupported) script from Microsoft that you can use to recollate
a database. Drop me a mail if you want it.
--
Jacco Schalkwijk
SQL Server MVP
"Jack" <jbonapart@.dicon.nl> wrote in message
news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
> Hi Folks,
> Is there another way to change the default collation for a
> user database without using the SQL cmd:
> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
> With the ALTER DATABASE it is not possible to change the
> database collation whenever there are Check Constraint
> which are depending on the database collation.
> A go lead for me would also be if someone can tell me in
> which master table this is recorded'
> Cheers Jack.|||Jacco,
What e-mail adres can I reach you ? I have tried
nospamjaccos@.eurostop.co.uk but i receive a Delivery
Status Notification Failure.
You can e-mail me the collate script to jbonapart@.dicon.nl
cheers.
Delivery to the following recipients failed.
>--Original Message--
>You have to drop the constraints, then change the
database collation, and
>recreate the constraints. Don't try to change anything in
system table
>directly, you can make your database completely unusable.
>I have an (unsupported) script from Microsoft that you
can use to recollate
>a database. Drop me a mail if you want it.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jack" <jbonapart@.dicon.nl> wrote in message
>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me in
>> which master table this is recorded'
>> Cheers Jack.
>
>.
>|||sorry, i had put a wrong sender's E-mail adres.
>--Original Message--
>Jacco,
>What e-mail adres can I reach you ? I have tried
>nospamjaccos@.eurostop.co.uk but i receive a Delivery
>Status Notification Failure.
>You can e-mail me the collate script to jbonapart@.dicon.nl
>cheers.
>Delivery to the following recipients failed.
>
>>--Original Message--
>>You have to drop the constraints, then change the
>database collation, and
>>recreate the constraints. Don't try to change anything
in
>system table
>>directly, you can make your database completely unusable.
>>I have an (unsupported) script from Microsoft that you
>can use to recollate
>>a database. Drop me a mail if you want it.
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Jack" <jbonapart@.dicon.nl> wrote in message
>>news:051301c3c2f8$46849d80$a301280a@.phx.gbl...
>> Hi Folks,
>> Is there another way to change the default collation
>for a
>> user database without using the SQL cmd:
>> ALTER DATABASE myDatase COLLATE Latin1_General_CI_AS '
>> With the ALTER DATABASE it is not possible to change
the
>> database collation whenever there are Check Constraint
>> which are depending on the database collation.
>> A go lead for me would also be if someone can tell me
in
>> which master table this is recorded'
>> Cheers Jack.
>>
>>.
>.
>sql

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...
Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...
|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nvar.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:
> I have a table with a column of datatype var and it has to be changed to nvar.
> The table contains millions of records and with an alter table my log get's
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatype.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to free
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
> > I have a table with a column of datatype var and it has to be changed to nvar.
> > The table contains millions of records and with an alter table my log get's
> > full, starts growing and in the end I my query stops with no result.
> > What is the best way to accomplish this change while keeping the data and
> > having not to much logging?
> >
> > Any help is appreciated.
> > I was thinking of an insert into a new table...|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
> Notice my suggestion might still fail finally, and you should be sure of the
> implication of changing recovery model.
> James
> "James Ma" wrote:
> > Make sure you have good backup firstly before doing what I can think out:
> > 1) If there are index on the column, drop them before changing its datatype.
> > 2) change the db recovery mode to 'simple'.
> > 3) shrink the db size and some other db size (including tempdb, or
> > considering change their recovery mode to 'simple' before shrinking) to free
> > more disk space.
> > 4) do the altering
> > 5) re-create the index.
> >
> > James
> >
> > "Zekske" wrote:
> >
> > > I have a table with a column of datatype var and it has to be changed to nvar.
> > > The table contains millions of records and with an alter table my log get's
> > > full, starts growing and in the end I my query stops with no result.
> > > What is the best way to accomplish this change while keeping the data and
> > > having not to much logging?
> > >
> > > Any help is appreciated.
> > > I was thinking of an insert into a new table...|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>> Notice my suggestion might still fail finally, and you should be sure of the
>> implication of changing recovery model.
>> James
>> "James Ma" wrote:
>> > Make sure you have good backup firstly before doing what I can think out:
>> > 1) If there are index on the column, drop them before changing its datatype.
>> > 2) change the db recovery mode to 'simple'.
>> > 3) shrink the db size and some other db size (including tempdb, or
>> > considering change their recovery mode to 'simple' before shrinking) to free
>> > more disk space.
>> > 4) do the altering
>> > 5) re-create the index.
>> >
>> > James
>> >
>> > "Zekske" wrote:
>> >
>> > > I have a table with a column of datatype var and it has to be changed to nvar.
>> > > The table contains millions of records and with an alter table my log get's
>> > > full, starts growing and in the end I my query stops with no result.
>> > > What is the best way to accomplish this change while keeping the data and
>> > > having not to much logging?
>> > >
>> > > Any help is appreciated.
>> > > I was thinking of an insert into a new table...|||We used alter table...
"Tibor Karaszi" wrote:
> Did you use EM or ALTER TABLE to do the change. EM tends to do these changes in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
> >I did change the recovery model to simple so that doesn't help.
> > That's why I was thinking about doing a bulk import or something like that
> > but I'm not sure it's possible.
> >
> > Thanks anyway
> >
> > "James Ma" wrote:
> >
> >> Notice my suggestion might still fail finally, and you should be sure of the
> >> implication of changing recovery model.
> >>
> >> James
> >>
> >> "James Ma" wrote:
> >>
> >> > Make sure you have good backup firstly before doing what I can think out:
> >> > 1) If there are index on the column, drop them before changing its datatype.
> >> > 2) change the db recovery mode to 'simple'.
> >> > 3) shrink the db size and some other db size (including tempdb, or
> >> > considering change their recovery mode to 'simple' before shrinking) to free
> >> > more disk space.
> >> > 4) do the altering
> >> > 5) re-create the index.
> >> >
> >> > James
> >> >
> >> > "Zekske" wrote:
> >> >
> >> > > I have a table with a column of datatype var and it has to be changed to nvar.
> >> > > The table contains millions of records and with an alter table my log get's
> >> > > full, starts growing and in the end I my query stops with no result.
> >> > > What is the best way to accomplish this change while keeping the data and
> >> > > having not to much logging?
> >> > >
> >> > > Any help is appreciated.
> >> > > I was thinking of an insert into a new table...
>
>

Change datatype var to nvar but log gets full

I have a table with a column of datatype var and it has to be changed to nva
r.
The table contains millions of records and with an alter table my log get's
full, starts growing and in the end I my query stops with no result.
What is the best way to accomplish this change while keeping the data and
having not to much logging?
Any help is appreciated.
I was thinking of an insert into a new table...Make sure you have good backup firstly before doing what I can think out:
1) If there are index on the column, drop them before changing its datatype.
2) change the db recovery mode to 'simple'.
3) shrink the db size and some other db size (including tempdb, or
considering change their recovery mode to 'simple' before shrinking) to free
more disk space.
4) do the altering
5) re-create the index.
James
"Zekske" wrote:

> I have a table with a column of datatype var and it has to be changed to n
var.
> The table contains millions of records and with an alter table my log get'
s
> full, starts growing and in the end I my query stops with no result.
> What is the best way to accomplish this change while keeping the data and
> having not to much logging?
> Any help is appreciated.
> I was thinking of an insert into a new table...|||Notice my suggestion might still fail finally, and you should be sure of the
implication of changing recovery model.
James
"James Ma" wrote:
[vbcol=seagreen]
> Make sure you have good backup firstly before doing what I can think out:
> 1) If there are index on the column, drop them before changing its datatyp
e.
> 2) change the db recovery mode to 'simple'.
> 3) shrink the db size and some other db size (including tempdb, or
> considering change their recovery mode to 'simple' before shrinking) to fr
ee
> more disk space.
> 4) do the altering
> 5) re-create the index.
> James
> "Zekske" wrote:
>|||I did change the recovery model to simple so that doesn't help.
That's why I was thinking about doing a bulk import or something like that
but I'm not sure it's possible.
Thanks anyway
"James Ma" wrote:
[vbcol=seagreen]
> Notice my suggestion might still fail finally, and you should be sure of t
he
> implication of changing recovery model.
> James
> "James Ma" wrote:
>|||Did you use EM or ALTER TABLE to do the change. EM tends to do these changes
in a very clumsy way.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Zekske" <Zekske@.discussions.microsoft.com> wrote in message
news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...[vbcol=seagreen]
>I did change the recovery model to simple so that doesn't help.
> That's why I was thinking about doing a bulk import or something like that
> but I'm not sure it's possible.
> Thanks anyway
> "James Ma" wrote:
>|||We used alter table...
"Tibor Karaszi" wrote:

> Did you use EM or ALTER TABLE to do the change. EM tends to do these chang
es in a very clumsy way.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Zekske" <Zekske@.discussions.microsoft.com> wrote in message
> news:CEF96044-B288-4DF0-95C4-486F3CC4B7F6@.microsoft.com...
>
>sql

Change Column name with ALTER statement

Hi,
Quick question.
How do I change a column name with the ALTER TABLE statement without losing
the data?
Thanks
ChristianCheck out sp_rename in BooksOnLine
Andrew J. Kelly SQL MVP
"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:ew3DT27CFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Quick question.
> How do I change a column name with the ALTER TABLE statement without
> losing
> the data?
> Thanks
> Christian
>|||Sorry,
I found the answer, sp_rename
Thanks anyway.
CHR
"Christian Perthen" <abracadabara@.dontreplytothidress.com> wrote in
message news:ew3DT27CFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Quick question.
> How do I change a column name with the ALTER TABLE statement without
losing
> the data?
> Thanks
> Christian
>

Change column length

Can you alter a column and make it longer without dropping the columnFound the script!

Change column from datetime to int

I want to change a column from datetime to int. I'm using this T-SQL:
ALTER TABLE WebPages
ALTER COLUMN EndDate int;
I get this result:
Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type datetime to data type int,
table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
function to run this query.
If I use Enterprise Manager the change happens without error. However, I
need to do this conversion as part of a larger script so I need to do it in
code. Can anyone tell me what EM is doing behind the scenes that allows this
to succeed? Thank you!Hi Ron
Please always state what version you are using.
I assume you are using SQL 2000 since you referred to Enterprise Manager. If
you trace what SQL Server is doing when you change datetime to int in EM,
you will see that it is actually recreating the entire table, selecting from
the old table using convert for the EndDate column, inserting into a new
table, dropping the original table and renaming the new table to the old
name. All indexes and triggers need to be rebuilt. This can be quite a
time-consuming process for a large table, but it is do-able. Just not with a
single statement.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:%23LP8GFhVHHA.2212@.TK2MSFTNGP02.phx.gbl...
>I want to change a column from datetime to int. I'm using this T-SQL:
> ALTER TABLE WebPages
> ALTER COLUMN EndDate int;
> I get this result:
> Server: Msg 260, Level 16, State 1, Line 1
> Disallowed implicit conversion from data type datetime to data type int,
> table 'gIQInternetMaster.dbo.WebPages', column 'EndDate'. Use the CONVERT
> function to run this query.
> If I use Enterprise Manager the change happens without error. However, I
> need to do this conversion as part of a larger script so I need to do it
> in
> code. Can anyone tell me what EM is doing behind the scenes that allows
> this
> to succeed? Thank you!
>
>

Change collation of column with User-Defined Datatype

I need to change the collation of all columns in a table by using ALTER
TABLE...
One of the columns consists of a user-defined data type. How do I change the
collation of the column?Hi
"DBA72" wrote:
> I need to change the collation of all columns in a table by using ALTER
> TABLE...
> One of the columns consists of a user-defined data type. How do I change the
> collation of the column?
User defined types do no support COLLATE to change the collation in the
definition of the type or when they are used in the definition of the
collumn. They will therefore pick up the datatype from the database at the
time the table was created.
e.g.
CREATE DATABASE MyTest COLLATE Latin1_General_CI_AS
GO
USE MYtest
GO
sp_addtype 'udt_test', 'varchar(10)', null
GO
CREATE TABLE mYtest1 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
ALTER DATABASE MyTest COLLATE SQL_Latin1_General_CP1_CI_AS
GO
USE MYtest
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
CREATE TABLE mYtest2 ( col1 udt_test )
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest2'
GO
-- To change the collation to the current database default then alter the
table
ALTER TABLE mYtest1 ALTER COLUMN col1 udt_test
GO
SELECT TABLE_NAME,COLLATION_NAME FROM information_schema.columns
WHERE TABLE_NAME = 'Mytest1'
GO
USE MASTER
GO
DROP DATABASE MyTest
GO
John

Cast & Convert

hi, how to cast or convert an nvarchar into a datetime ...'
as in: (see WHEN 'datecreated' then Cast(d_date_created as datetime))
ALTER PROCEDURE [dbo].[SelectContactListTEST]
@.activeSearch bit,
@.activeSearch2 bit,
@.hiddenSearch bit,
@.orderingBy nvarchar (255),
@.orderingWay nvarchar (255)
AS
SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid, UO.company AS
company, UO.ContOwner AS contowner, UO.u_first_name AS firstname,
UO.u_last_name AS lastname, UO.u_email_address AS
email, UO.Hidden AS hidden, UO.Activated AS activated, UO.d_date_created AS
datecreated,
A.u_city AS city, A.u_country_code AS country,
A.u_region_code AS region
FROM UserObject UO INNER JOIN
Addresses A ON UO.g_user_id = A.g_id
WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN
(@.activeSearch,@.activeSearch2)) AND (UO.Hidden = @.hiddenSearch)
ORDER BY
case @.orderingWay
when 'desc' then
CASE @.orderingBy
WHEN 'company' then company
WHEN 'lastname' then u_last_name
WHEN 'country' then u_country_code
WHEN 'region' then u_region_code
WHEN 'city' then u_city
WHEN 'email' then u_email_address
WHEN 'activated' then Cast(Activated as Char(1))
WHEN 'datecreated' then Cast(d_date_created as datetime)
end
END
DESCPlease don't start another thread directly related to one that is still
active. And please read the information posted in reply. Hugo has already
posted VERY relevant information - case returns a scalar value and all
possible resulting expressions are converted to the same datatype. This is
the crux of your problem. You cannot use a single expression to order by
both character strings and datetime. This is the same issue as the previous
"bit" problem.
"lp_rochon" <lprochon@.discussions.microsoft.com> wrote in message
news:450908D4-5A0C-4D86-8823-44FAF9991660@.microsoft.com...
> hi, how to cast or convert an nvarchar into a datetime ...'
>
> as in: (see WHEN 'datecreated' then Cast(d_date_created as datetime))
> --
> ALTER PROCEDURE [dbo].[SelectContactListTEST]
> @.activeSearch bit,
> @.activeSearch2 bit,
> @.hiddenSearch bit,
> @.orderingBy nvarchar (255),
> @.orderingWay nvarchar (255)
> AS
> SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid, UO.company AS
> company, UO.ContOwner AS contowner, UO.u_first_name AS firstname,
> UO.u_last_name AS lastname, UO.u_email_address AS
> email, UO.Hidden AS hidden, UO.Activated AS activated, UO.d_date_created
AS
> datecreated,
> A.u_city AS city, A.u_country_code AS country,
> A.u_region_code AS region
> FROM UserObject UO INNER JOIN
> Addresses A ON UO.g_user_id = A.g_id
> WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN
> (@.activeSearch,@.activeSearch2)) AND (UO.Hidden = @.hiddenSearch)
> ORDER BY
> case @.orderingWay
> when 'desc' then
> CASE @.orderingBy
> WHEN 'company' then company
> WHEN 'lastname' then u_last_name
> WHEN 'country' then u_country_code
> WHEN 'region' then u_region_code
> WHEN 'city' then u_city
> WHEN 'email' then u_email_address
> WHEN 'activated' then Cast(Activated as Char(1))
> WHEN 'datecreated' then Cast(d_date_created as datetime)
> end
> END
> DESC|||> hi, how to cast or convert an nvarchar into a datetime ...'
select cast(N'2005-02-08T14:40:00' as datetime)
What is the problem?
AMB
"lp_rochon" wrote:

> hi, how to cast or convert an nvarchar into a datetime ...'
>
> as in: (see WHEN 'datecreated' then Cast(d_date_created as datetime))
> --
> ALTER PROCEDURE [dbo].[SelectContactListTEST]
> @.activeSearch bit,
> @.activeSearch2 bit,
> @.hiddenSearch bit,
> @.orderingBy nvarchar (255),
> @.orderingWay nvarchar (255)
> AS
> SELECT UO.g_user_id AS userid, UO.g_org_id AS orgid, UO.company AS
> company, UO.ContOwner AS contowner, UO.u_first_name AS firstname,
> UO.u_last_name AS lastname, UO.u_email_address AS
> email, UO.Hidden AS hidden, UO.Activated AS activated, UO.d_date_created A
S
> datecreated,
> A.u_city AS city, A.u_country_code AS country,
> A.u_region_code AS region
> FROM UserObject UO INNER JOIN
> Addresses A ON UO.g_user_id = A.g_id
> WHERE (UO.customer_type = 'wholesaler') AND (UO.Activated IN
> (@.activeSearch,@.activeSearch2)) AND (UO.Hidden = @.hiddenSearch)
> ORDER BY
> case @.orderingWay
> when 'desc' then
> CASE @.orderingBy
> WHEN 'company' then company
> WHEN 'lastname' then u_last_name
> WHEN 'country' then u_country_code
> WHEN 'region' then u_region_code
> WHEN 'city' then u_city
> WHEN 'email' then u_email_address
> WHEN 'activated' then Cast(Activated as Char(1))
> WHEN 'datecreated' then Cast(d_date_created as datetime)
> end
> END
> DESC