DateTime Error

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

You should convert src_terrier.datadate not to datetime (it IS datedime, after all :) ), but to varchar. And no, you don't need N in this case.

HTH :)

|||Hi,

first of all, this is right, you don′t need to N the datestring. If you want to pass the "datestring as a string, I would rather prefer passing it as ISO date e.g. 20040101 rather than in a formatted way. Then you need to convert it to VARCHAR if you use a format / convert function. The isu code for formatting to 20040101 is 112, so this should be something like

WHERE (CONVERT(VARCHAR(10),src_terrier.datadate,112) = @.dt_src_date) AND..........

The other option you could use, which would normally better keep track of an index rather than convert all the datetimes stored in your table. Use datediff, it should be 0 if you compare by days and the record is of the same day as the imput paramter.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Is src_terrier.datadate column smalldatetime/datetime? If not you should first change it to one of the datetime data types so you can manipulate the data efficiently and perform searches in meaningful manner. As for the input, you can use the ISO unseparated format as mentioned so the code works under any language settings. If you are going to pass the value in a specific format then perform a conversion in the code like:

convert(datetime, @.dt_src_date, 101 )

convert(datetime, @.dt_src_date, 103)

If you try there and it doesn't resolve the problem then please post a sample script with DDL & data so it is easy to see what the schema looks like and the actual code.