convert string to datetime in function SQL Server

I have been writing a user-defined function in SQL Server:

It looks like this :

CREATE FUNCTION FormatDate(@fromtime nvarchar(50))
RETURNS DATETIME
AS 
BEGIN

    DECLARE @tempfrom datetime
    DECLARE @tempto nvarchar(50)

    set @tempfrom =  Convert(datetime, @fromtime, 100) 
    RETURN @tempf开发者_运维技巧rom

END

select dbo.FormatDate('08/17/2010 4:30')

When I try to run this, I get the following error:

Conversion failed when converting the nvarchar value '08/17/2010 4:30' to data type int.

What am I doing wrong?

The 100 you're specifying in the convert is used to format when you're selecting data, not to format the storage of data.

Datetime is just stored as datetime - that's it (i mean, dependent on your SQL settings, it might be MM/DD/YYYY or DD/MM/YYYY).

But if you just do this:

set @tempfrom =  Convert(datetime, @fromtime)

it's now a datetime, which you can convert to your desired formatting by wrapping it in another convert:

convert(varchar, convert(datetime, @fromtime), 100)

Why are you using style 100?

CREATE FUNCTION dbo.FormatDate -- schema prefix always!
(
    @fromtime VARCHAR(50) -- varchar
)
RETURNS DATETIME
AS 
BEGIN -- don't need variables anywhere
   RETURN(CONVERT(DATETIME, @fromtime));
END
GO