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...
>

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 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 character
s.
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...
>

Change Data type from Nvarchar to datetime

i have a sql db in production and i need to change the data type of one of m
y
fields. Can this be done? I have made several attempts but have failed.Probably because you have data in there that is not a valid datetime. This
is a common symptom for using a catch-all datatype like nvarchar for storing
inappropriate data, whether it be to ease the development/validation of end
user interfaces or to seamlessly integrate legacy data.
"Failed" could mean many things to me. Are you just saying ALTER TABLE
tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to a
conversion issue, then you may want to consider trying this:
-- how many are bad:
SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
-- a few samples:
SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
You might be really hosed, or you my just have to tweak the data before
altering the table, by converting to a valid datetime format. It's tough to
get any more specific without valid DDL and sample data (see
http://www.aspfaq.com/5006
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>i have a sql db in production and i need to change the data type of one of
>my
> fields. Can this be done? I have made several attempts but have failed.|||Thank you for your suggestion. However, I do not have any data in this
particular field. This is the error I get...
'Contacts' table
- Warning: Data might be lost converting column 'MaritalStatus' from
'nvarchar(20)'.
The MaritalStatus field does not have any data. I would like to rename it
to PromoCodeEmail and change the data type to datetime.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Probably because you have data in there that is not a valid datetime. Thi
s
> is a common symptom for using a catch-all datatype like nvarchar for stori
ng
> inappropriate data, whether it be to ease the development/validation of en
d
> user interfaces or to seamlessly integrate legacy data.
> "Failed" could mean many things to me. Are you just saying ALTER TABLE
> tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to
a
> conversion issue, then you may want to consider trying this:
> -- how many are bad:
> SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
> -- a few samples:
> SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
> You might be really hosed, or you my just have to tweak the data before
> altering the table, by converting to a valid datetime format. It's tough
to
> get any more specific without valid DDL and sample data (see
> http://www.aspfaq.com/5006
>
>
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>
>|||Joe,
First, thats a warning, not an error. The warning is generated because
changing data types can result in loss of data, but that doesn't necessarily
mean they will.
Second, if you have a column that is not used, drop the column and add a new
column. There is no need to rename it and change the data type.
You are best off using a script for these changes rather than using the gui.
Scripts can be run against your development and test databases and debugged
before runnign them against production. Scripts also insure that the
changes made in all databases are the same, without having to worry about
user error from clicking on the wrong field in enterprise manager.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:82D1F9C5-9AF9-4D3E-AC46-886F0E64CB4B@.microsoft.com...
> Thank you for your suggestion. However, I do not have any data in this
> particular field. This is the error I get...
> 'Contacts' table
> - Warning: Data might be lost converting column 'MaritalStatus' from
> 'nvarchar(20)'.
> The MaritalStatus field does not have any data. I would like to rename it
> to PromoCodeEmail and change the data type to datetime.
> "Aaron Bertrand [SQL Server MVP]" wrote:
>
This
storing
end
to a
tough to
of
failed.|||I have never written a script before. Is there some literature that will
assist me in doing you suggested?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Probably because you have data in there that is not a valid datetime. Thi
s
> is a common symptom for using a catch-all datatype like nvarchar for stori
ng
> inappropriate data, whether it be to ease the development/validation of en
d
> user interfaces or to seamlessly integrate legacy data.
> "Failed" could mean many things to me. Are you just saying ALTER TABLE
> tablename ALTER COLUMN columnname DATETIME? If so, and that fails due to
a
> conversion issue, then you may want to consider trying this:
> -- how many are bad:
> SELECT COUNT(*) FROM TableName WHERE IsDate(ColumnName)=0;
> -- a few samples:
> SELECT TOP 50 ColumnName FROM TableName WHERE ISDate(ColumnName)=0;
> You might be really hosed, or you my just have to tweak the data before
> altering the table, by converting to a valid datetime format. It's tough
to
> get any more specific without valid DDL and sample data (see
> http://www.aspfaq.com/5006
>
>
>
>
> "Joe" <Joe@.discussions.microsoft.com> wrote in message
> news:6BB5D430-FFF8-4BC9-BF59-DA27B3B83615@.microsoft.com...
>
>|||What part of my suggestion are you having difficulty with?
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:356065F7-EB6A-4304-9448-ECF8B0FB40F7@.microsoft.com...
>I have never written a script before. Is there some literature that will
> assist me in doing you suggested?
> "Aaron Bertrand [SQL Server MVP]" wrote:
>

CAST error with nvarchar to int (Error converting data type nvarchar to int)

I am receiving an error with a transact query when performing a CAST from an nvarchar to int. For example:

SELECT myField1, CAST(myField2 as int) FROM tbl_myTable

MSDN article describe that this casting error "Error converting data type nvarchar to float" is caused by an invalid non-numeric entry.

Is there a way to perform an in-line test to perform a check to determine a course of action? For example:

SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Are above expression, or something similar to it, even possible in transact-SQL?

Thank you,
Each column in a select stmt can only be one data type. Why do you need to check each row to see if it is an int or not?|||Due to requirements, it is necessary to "transform" data from nvarchar into int (in a massive sweep).

I think I have been able to figure this out using CAST

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASECAST isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||

CAST and CONVERT are the same, CAST is just the standard.

Code Snippet

SELECT field1, CASE WHEN IsNumeric(myField2) = 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM table

anything other than NULL and int wont work though.|||Sorry for a bit of a typo, meant to write the following:

Instead of : SELECT myField1, IIF(isNumber(myField2) = 1, CAST(myField2 as int),0) FROM tbl_myTable

Following I think will work:
SELECT myFIeld1, CASE isNumeric(myField2) WHEN 1 THEN CAST(myField2 as int) ELSE NULL END as myField2 FROM tbl_myTable

What do you think?
|||yes, that works fine|||Thank you to everyone who replied!

CAST boolean to nvarchar

I have a boolean field I need to CAST to an nvarchar field of either "true"
or "false". When I use CAST(field1 AS nvarchar(50)) it results in either a
0 or 1. What's the best way using T-SQL to do this?
Thanks,
Ryn
There is no BOOLEAN type in SQL Server. I assume you mean BIT (this is NOT
BOOLEAN!). A BIT stores 0 or 1, and these values have no correlation to
true or false (except for the cognitive relation *you* make). Try the
following instead of assuming than a conversion to nvarchar(50) will work
magic. BTW, why do you need Unicode and why do you need 50 spaces, when
your outcome is limited to 'true' and 'false'?
SELECT
Field1,
Field1_As_Boolean = CONVERT
(
VARCHAR(5),
CASE field1 WHEN 1 THEN 'true' ELSE 'false' END
)
FROM table_name;
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>I have a boolean field I need to CAST to an nvarchar field of either "true"
>or "false". When I use CAST(field1 AS nvarchar(50)) it results in either a
>0 or 1. What's the best way using T-SQL to do this?
> Thanks,
> Ryn
>
|||Use CASE
CASE MyBoo
WHEN 0 THEN 'TRUE'
WHEN 1 THEN 'FALSE'
ELSE 'OOPS'
END
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
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>I have a boolean field I need to CAST to an nvarchar field of either "true"
> or "false". When I use CAST(field1 AS nvarchar(50)) it results in either a
> 0 or 1. What's the best way using T-SQL to do this?
> Thanks,
> Ryn
>
|||Yes I do mean bit.. old habits die hard I'm doing a UNION with this
select statement with others that have 50 character nvarchar fields. This
should work, thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ulna7N0BHHA.5068@.TK2MSFTNGP02.phx.gbl...
> There is no BOOLEAN type in SQL Server. I assume you mean BIT (this is
> NOT BOOLEAN!). A BIT stores 0 or 1, and these values have no correlation
> to true or false (except for the cognitive relation *you* make). Try the
> following instead of assuming than a conversion to nvarchar(50) will work
> magic. BTW, why do you need Unicode and why do you need 50 spaces, when
> your outcome is limited to 'true' and 'false'?
> SELECT
> Field1,
> Field1_As_Boolean = CONVERT
> (
> VARCHAR(5),
> CASE field1 WHEN 1 THEN 'true' ELSE 'false' END
> )
> FROM table_name;
>
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>

CAST boolean to nvarchar

I have a boolean field I need to CAST to an nvarchar field of either "true"
or "false". When I use CAST(field1 AS nvarchar(50)) it results in either a
0 or 1. What's the best way using T-SQL to do this?
Thanks,
RynThere is no BOOLEAN type in SQL Server. I assume you mean BIT (this is NOT
BOOLEAN!). A BIT stores 0 or 1, and these values have no correlation to
true or false (except for the cognitive relation *you* make). Try the
following instead of assuming than a conversion to nvarchar(50) will work
magic. BTW, why do you need Unicode and why do you need 50 spaces, when
your outcome is limited to 'true' and 'false'?
SELECT
Field1,
Field1_As_Boolean = CONVERT
(
VARCHAR(5),
CASE field1 WHEN 1 THEN 'true' ELSE 'false' END
)
FROM table_name;
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>I have a boolean field I need to CAST to an nvarchar field of either "true"
>or "false". When I use CAST(field1 AS nvarchar(50)) it results in either a
>0 or 1. What's the best way using T-SQL to do this?
> Thanks,
> Ryn
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0197_01C70700.3535C820
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Use CASE
CASE MyBoo
WHEN 0 THEN 'TRUE'
WHEN 1 THEN 'FALSE'
ELSE 'OOPS'
END
-- 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
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message =news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>I have a boolean field I need to CAST to an nvarchar field of either ="true" > or "false". When I use CAST(field1 AS nvarchar(50)) it results in =either a > 0 or 1. What's the best way using T-SQL to do this?
> > Thanks,
> Ryn > >
--=_NextPart_000_0197_01C70700.3535C820
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Use CASE
CASE MyBoo
WHEN 0 THEN 'TRUE'
WHEN 1 THEN 'FALSE'
ELSE ='OOPS'
END
-- 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
"Ryan" wrote in message news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...>I =have a boolean field I need to CAST to an nvarchar field of either "true" > or "false". When I use CAST(field1 AS nvarchar(50)) it results in =either a > 0 or 1. What's the best way using T-SQL to do =this?> > Thanks,> Ryn > >

---

--=_NextPart_000_0197_01C70700.3535C820--|||Yes I do mean bit.. old habits die hard :) I'm doing a UNION with this
select statement with others that have 50 character nvarchar fields. This
should work, thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ulna7N0BHHA.5068@.TK2MSFTNGP02.phx.gbl...
> There is no BOOLEAN type in SQL Server. I assume you mean BIT (this is
> NOT BOOLEAN!). A BIT stores 0 or 1, and these values have no correlation
> to true or false (except for the cognitive relation *you* make). Try the
> following instead of assuming than a conversion to nvarchar(50) will work
> magic. BTW, why do you need Unicode and why do you need 50 spaces, when
> your outcome is limited to 'true' and 'false'?
> SELECT
> Field1,
> Field1_As_Boolean = CONVERT
> (
> VARCHAR(5),
> CASE field1 WHEN 1 THEN 'true' ELSE 'false' END
> )
> FROM table_name;
>
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>>I have a boolean field I need to CAST to an nvarchar field of either
>>"true" or "false". When I use CAST(field1 AS nvarchar(50)) it results in
>>either a 0 or 1. What's the best way using T-SQL to do this?
>> Thanks,
>> Ryn
>

CAST boolean to nvarchar

I have a boolean field I need to CAST to an nvarchar field of either "true"
or "false". When I use CAST(field1 AS nvarchar(50)) it results in either a
0 or 1. What's the best way using T-SQL to do this?
Thanks,
RynThere is no BOOLEAN type in SQL Server. I assume you mean BIT (this is NOT
BOOLEAN!). A BIT stores 0 or 1, and these values have no correlation to
true or false (except for the cognitive relation *you* make). Try the
following instead of assuming than a conversion to nvarchar(50) will work
magic. BTW, why do you need Unicode and why do you need 50 spaces, when
your outcome is limited to 'true' and 'false'?
SELECT
Field1,
Field1_As_Boolean = CONVERT
(
VARCHAR(5),
CASE field1 WHEN 1 THEN 'true' ELSE 'false' END
)
FROM table_name;
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message
news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>I have a boolean field I need to CAST to an nvarchar field of either "true"
>or "false". When I use CAST(field1 AS nvarchar(50)) it results in either a
>0 or 1. What's the best way using T-SQL to do this?
> Thanks,
> Ryn
>|||Use CASE
CASE MyBoo
WHEN 0 THEN 'TRUE'
WHEN 1 THEN 'FALSE'
ELSE 'OOPS'
END
--
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
"Ryan" <Tyveil@.newsgroups.nospam> wrote in message news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gb
l...
>I have a boolean field I need to CAST to an nvarchar field of either "true"
> or "false". When I use CAST(field1 AS nvarchar(50)) it results in either
a
> 0 or 1. What's the best way using T-SQL to do this?
>
> Thanks,
> Ryn
>
>|||Yes I do mean bit.. old habits die hard I'm doing a UNION with this
select statement with others that have 50 character nvarchar fields. This
should work, thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:ulna7N0BHHA.5068@.TK2MSFTNGP02.phx.gbl...
> There is no BOOLEAN type in SQL Server. I assume you mean BIT (this is
> NOT BOOLEAN!). A BIT stores 0 or 1, and these values have no correlation
> to true or false (except for the cognitive relation *you* make). Try the
> following instead of assuming than a conversion to nvarchar(50) will work
> magic. BTW, why do you need Unicode and why do you need 50 spaces, when
> your outcome is limited to 'true' and 'false'?
> SELECT
> Field1,
> Field1_As_Boolean = CONVERT
> (
> VARCHAR(5),
> CASE field1 WHEN 1 THEN 'true' ELSE 'false' END
> )
> FROM table_name;
>
>
> "Ryan" <Tyveil@.newsgroups.nospam> wrote in message
> news:OICNDF0BHHA.4348@.TK2MSFTNGP04.phx.gbl...
>

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