DateTime Format

[Ice Rhino]

I have a function that is based aounr the input of parameters. The last remaing issue is that I am required to enter the data into the parameter field as mm/dd/yyyy. I want to be able to enter the data as dd/mm/yyyy. I have tried to use

WHERE (CONVERT(datetime,src_terrier.datadate,103) = @dt_src_date) AND..........

But this just throws an error "Msg 8114, Level 16, State 1, Procedure spWTRalldatareportsummary, Line 0
Error converting data type nvarchar to datetime."

The execution line I am using is

USE [DashboardSQL-2K5]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[spWTRalldatareportsummary]
@dt_src_date = N'28/04/2006',
@chr_div = NULL,
@vch_portfolio_no = NULL,
@vch_prop_cat = NULL

SELECT 'Return Value' = @return_value

GO
Anybody got any ideas as to what I have done wrong? I have also tried it without the N just before the date and get a varchar version of the same error.

Thanks in advance

 

[giovi2002]

first convert the field to varchar and use the left function to make sure you'll only capture 10 characters
Something like this


Then add the convert to datetime function

convert(datetime,left(convert(varchar,yourdatetfield),10),103)

Maybe that will work