Error converting data type varchar to datetime.

Question

Post reply

Error converting data type varchar to datetime.

gary1

SSC-Insane

Points: 21152
More actions
September 3, 2009 at 9:48 pm

#97989

Hi,
Exec [dbo].[sp_BackupAllFull] @MarkName nvarchar(8), @BackupPath nvarchar(128), @TimeStamp datetime = NULL,@UseLocalTime bit=0
here I'm passing the parameters as below (giving zero as time stamp value to get local time)
EXEC [dbo].[sp_BackupAllFull] 'test', 'c:\', '0'
error:
Msg 8114, Level 16, State 5, Procedure sp_BackupAllFull, Line 0
Error converting data type varchar to datetime.
what value should I give for @timestamp parameter to get local time?

Viewing 5 posts - 1 through 4 (of 4 total)

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

psvirin Mr or Mrs. 500 Points: 542 More actions · Answer 1

Have you tried this one:

EXEC [dbo].[sp_BackupAllFull] 'test', 'c:\', NULL, '0'

OR

EXEC [dbo].[sp_BackupAllFull] 'test', 'c:\'

because all of the last argumants have default values

Hope that helps

-----------------

StarWind Software developer ( http://www.starwindsoftware.com )

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 2

exec [dbo].[sp_BackupAllFull]

@MarkName nvarchar(8),

@BackupPath nvarchar(128),

@TimeStamp datetime = NULL,

@UseLocalTime bit=0

if you are trying to pass less parameters then procedure require and you omit some in middle you must specify all parameters names. in your case it should looks like:

EXEC [dbo].[sp_BackupAllFull]

@MarkName='test',

@BackupPath='c:\',

@UseLocalTime= '0'

Paul White SSC Guru Points: 150467 More actions · Answer 3

Marcin Gol [SQL Server MVP] (9/6/2009)
exec [dbo].[sp_BackupAllFull] @MarkName nvarchar(8), @BackupPath nvarchar(128), @TimeStamp datetime = NULL,
@UseLocalTime bit=0
if you are trying to pass less parameters then procedure require and you omit some in middle you must specify all parameters names. in your case it should looks like:
EXEC [dbo].[sp_BackupAllFull]
@MarkName='test',
@BackupPath='c:\',
@UseLocalTime= '0'

Best practice is to respect the parameter data types to avoid implicit conversions as far as possible:

EXEC [dbo].[sp_BackupAllFull]

@MarkName = N'test',

@BackupPath = N'c:\',

@UseLocalTime = 0;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Marcin Gol Hall of Fame Points: 3314 More actions · Answer 4

Paul White (9/6/2009)Best practice is to respect the parameter data types to avoid implicit conversions as far as possible:
EXEC [dbo].[sp_BackupAllFull]
@MarkName = N'test',
@BackupPath = N'c:\',
@UseLocalTime = 0;

Paul - of course you are right!