Converting a nvarchar parameter to datetime

i've got a stored procedure that gets a parameter as nvarchar, and
executes another procedure that needs the same parameter , but as
datetime
i've tried this: (where @.plantingDate is the parameter i get as
nvarchar)
CAST(@.PlantingDate as datetime)
and this:
CONVERT(datetime,@.PlantingDate)
but i get an error message....
what is the right way to do it'What values are you passing in for @.plantingDate? Can you give a couple of
examples of @.plantingDate values?
<friedman30@.gmail.com> wrote in message
news:1148223008.723472.278160@.j73g2000cwa.googlegroups.com...
> i've got a stored procedure that gets a parameter as nvarchar, and
> executes another procedure that needs the same parameter , but as
> datetime
> i've tried this: (where @.plantingDate is the parameter i get as
> nvarchar)
> CAST(@.PlantingDate as datetime)
> and this:
> CONVERT(datetime,@.PlantingDate)
> but i get an error message....
> what is the right way to do it'
>|||On 21 May 2006 07:50:08 -0700, friedman30@.gmail.com wrote:
>i've got a stored procedure that gets a parameter as nvarchar, and
>executes another procedure that needs the same parameter , but as
>datetime
>i've tried this: (where @.plantingDate is the parameter i get as
>nvarchar)
>CAST(@.PlantingDate as datetime)
>and this:
>CONVERT(datetime,@.PlantingDate)
>but i get an error message....
>what is the right way to do it'
Hi friedman30,
If you use CAST(@.PlantingDate AS datetime), the format @.PlantingDate has
to be one of the following:
* yyyymmdd (date only - no dashes, slashes, dots or other punctuation!!)
* yyyy-mm-ddThh:mm:ss (date and time - note the punctuation and the
uppercase "T" between date and time part)
* yyyy-mm-ddThh:mm:ss.mmm (date and time, including milliseconds).
With other formats (such as 03/04/05), it's up to the wisdom of SQL
Server te guess if this is inteded to be mm/ddd/yy, dd/mm/yy, or even
yy/mm/dd. Due to Murphy's Law, SQL Server will probably guess right in
development and wrong in production. <g>
If your nvarchar date is not in one of the above formats, then you can
use CONVERT with a style parameter to force SQL Server to use the chosen
style. E.g. CONVERT(datetime, '03/04/05', 11) will always be evaluated
to April 3rd, 2005. For a full list of style paramters and the
associated date and time formats, see CONVERT in Books Online.
--
Hugo Kornelis, SQL Server MVP|||THANKSsqlsql