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