Error Executing the Convert Function. Please assist.

  • I am getting some weird error while running the below script. The error is typically coming, at the below line:

    SELECT @fileDate = CONVERT(VARCHAR(200),GETDATE(),113)

    but, I use '112' it is running fine.

    =================ERROR Message===================

    Msg 3201, Level 16, State 1, Line 11

    Cannot open backup device 'D:\backup\AdventureWorks_22 Nov 2010 19:29:35:003.BAK'. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

    Msg 3013, Level 16, State 1, Line 11

    BACKUP DATABASE is terminating abnormally.

    ================ **************** ===================

    Use master

    GO

    declare @filename varchar(400)

    declare @filedate varchar(300)

    declare @path varchar(300)

    declare @name varchar(100)

    Set @path = 'D:\backup\'

    set @name = 'AdventureWorks'

    SELECT @fileDate = CONVERT(VARCHAR(200),GETDATE(),113)

    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

    Backup Database AdventureWorks

    To Disk = @fileName

    go

    Thanks.

  • When you use style number 113, you get the date and time with spaces in it. When you use style 112, you don’t have spaces in the results. When you get spaces in the results, you get a file name with spaces in it, which is not aloud.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • also a valid file name cannot have colons in it('D:\backup\AdventureWorks_22 Nov 2010 19:29:35:003.BAK'), so you need to make sure the string you are building is valid for a filename.

    i usually use one of these two to build a sting similar to what you were doing:

    FormattedDate SQL

    20101122-09:15:58:667 SELECT CONVERT(VARCHAR(35),@date,112) + '-' + CONVERT(VARCHAR(35),@date,114)

    20101122-091558 SELECT CONVERT(VARCHAR(35),@date,112) + '-' + REPLACE(CONVERT(VARCHAR(35),@date,108),':','')

    20101122-091558667 SELECT CONVERT(VARCHAR(35),@date,112) + '-' + REPLACE(CONVERT(VARCHAR(35),@date,114),':','')

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks a lot Lowell.. I am now using the below string...which is still fine.

    [SELECT @fileDate = CONVERT(VARCHAR(300),GETDATE(),112) + '-' + REPLACE(CONVERT(VARCHAR(35),GETDATE(),114),':','')

    ]

    Use master

    GO

    declare @filename varchar(400)

    declare @filedate varchar(300)

    declare @path varchar(300)

    declare @name varchar(100)

    --declare @date datetime

    Set @path = 'D:\backup\'

    set @name = 'AdventureWorks'

    --set @date = GETDATE()

    --SELECT @fileDate = CONVERT(VARCHAR(200),GETDATE(),112)

    SELECT @fileDate = CONVERT(VARCHAR(300),GETDATE(),112) + '-' + REPLACE(CONVERT(VARCHAR(35),GETDATE(),114),':','')

    SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

    Backup Database AdventureWorks

    To Disk = @fileName

    go

    Thanks.

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

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