DateTime parmater for sp and debug

Question

Post reply

DateTime parmater for sp and debug

veloLewis

SSC Enthusiast

Points: 184
More actions
December 18, 2003 at 10:38 am

#83726

I have a SP that uses a input dateTime paramater. Does anyone happen to know what the proper format is to enter this paramater when using the T-SQL Debugger. I keep getting an invalid character value for cast specification on the different formats I have tried.

Viewing 10 posts - 1 through 9 (of 9 total)

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

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

Can you provide an example of the TSQL code you are using when calling the SP?

Once you understand the BITs, all the pieces come together

veloLewis SSC Enthusiast Points: 184 More actions · Answer 2

Not a problem when actually calling - just trying to use with T-SQL debugger and setting input paramaters. I have tried entering date as mm/dd/yyyy mm:hh:ss and different variations - but keep getting the inavlaid character in cast whenever I execute the sp with the debugger tool. The paramater list looks like this -

proc sp_segmentCount

@companyNum nvarchar (6),

@campaignID nvarchar (16),

@segmentID nvarchar (16),

@endRunDate datetime

ThomasH SSCertifiable Points: 7437 More actions · Answer 3

have you tried putting single quotes around the value? i.e. 'mm/dd/yyyy mm:hh:ss'

Once you understand the BITs, all the pieces come together

veloLewis SSC Enthusiast Points: 184 More actions · Answer 4

Yes - I have tried entering -

'12/18/2003 10:30:00', with and without time, single quotes etc. Must be something basic here, just not sure what it is. As a temporary fix to get around it and changed paramater to nvarchar and then do a cast to datetime - which does work.

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

I'm glad you got something to work. Let us know if you find out how to pass dates in the debugger.

Once you understand the BITs, all the pieces come together

SQLBill SSC Guru Points: 51440 More actions · Answer 6

Have you tried the format:

'yyyy-mm-dd hh:mm:ss'

i.e. '2003-12-18 18:30:00'

-SQLBill

jcool SSC Eights! Points: 863 More actions · Answer 7

I have found out that SQL Server does a correct implicit conversion from varchar to datetime if you use XML date format:

'yyyy-mm-ddThh:mm:ss' (note the letter T between date and time part)

I use it to fill datetime parameter of stored procedure. Works every time no matter what your regional settings, language or dateformat settings are.

I haven't tried on a debugger yet, tell me if it works.

Edited by - jcool on 12/19/2003 01:09:59 AM

Mark.bagnall@ultimedia.co.uk SSC-Addicted Points: 472 More actions · Answer 8

You need to use the following ANSI formats (including the curly brackets):

{ d 'yyyy-mm-ddd'} for date only

{ ts 'yyyy-mm-dd hh:mm:ss'} for date and time

I've been through the same hoops as you - it took me ages to find this in BOL.

Have a look at 'Rules for Entering Search Values' in BOL.

veloLewis SSC Enthusiast Points: 184 More actions · Answer 9

These are the formats the seem to work just fine when entering the parmaters in T-SQL debugger -

{ d 'yyyy-mm-ddd'} for date only

{ ts 'yyyy-mm-dd hh:mm:ss'} for date and time

Thanks as this would have taken forever to find, you would think there would be a little better documentation on this.