Post reply

Error converting data type varchar to datetime.

  • 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?

  • September 4, 2009 at 1:40 am

    #1049239

    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 )

  • September 6, 2009 at 12:41 pm

    #1049896

    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'

  • September 6, 2009 at 5:33 pm

    #1049932

    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

  • September 7, 2009 at 10:34 am

    #1050174

    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!

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

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