DateTime Error

Question

Post reply

DateTime Error

Toni Chaffin

SSCommitted

Points: 1513
More actions
May 13, 2006 at 11:18 pm

#172547

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

This should do it...

USE [DashboardSQL-2K5]

GO

DECLARE @return_value int

EXEC @return_value = [dbo].[spWTRalldatareportsummary]

@dt_src_date = CONVERT(DATETIME,N'28/04/2006',103),

@chr_div = NULL,

@vch_portfolio_no = NULL,

@vch_prop_cat = NULL

SELECT 'Return Value' = @return_value

GO

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Toni Chaffin SSCommitted Points: 1513 More actions · Answer 2

Thank you Jeff, I will give that a run now.

Watch this space!

Kindest Regards