none
Have file name with date working but appending the time to file name can't get a result RRS feed

  • Question

  • DECLARE @dt AS VARCHAR(26)
    	SELECT @dt = REPLACE(CONVERT(VARCHAR(26),getDate(),110),'-','-'

    Have this appended to a filename like: log_01-15-2015.  Don't know what to add to SQL code to get the get the time and append time in hours, min, sec. like:  log_01-15-2015_10:01:33  

    DECLARE @dt AS VARCHAR(50)
    
    SELECT @dt = REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,110),'-','-') +'_'+ CONVERT(VARCHAR,CURRENT_TIMESTAMP,108)
    
    EXEC master.dbo.sp_configure 'show advanced options',1
    RECONFIGURE WITH OVERRIDE
    
    EXEC master.dbo.sp_configure 'xp_cmdshell',1
    RECONFIGURE WITH OVERRIDE
    
    SET @bcpCommand = 'bcp "SELECT * FROM ##Temp ORDER BY barcode" queryout "C:\TEMP\Log\ContainerHistory_Log_' + @dt +'" -c -T'
    
    EXEC master..xp_cmdshell @bcpCommand
    I am trying to append to the file created with the date a time stamp but when appending the time part for the filename results aren't creating the file.  If it's just doing the date part then it is working. Any idea what is wrong? 



    • Edited by tonofit Friday, January 16, 2015 11:29 PM
    Thursday, January 15, 2015 10:35 PM

Answers

  • The character':' is an illegal character in the file name. Try this:

    'log_'+REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,101),'/','_')+'_'+REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,108),':','_')

    • Marked as answer by tonofit Saturday, January 17, 2015 6:52 AM
    Saturday, January 17, 2015 3:06 AM
  • Since you added ":" to the file name, the file name is not valid. You can change ":" to "-".

    DECLARE @dt AS VARCHAR(50)
    SELECT @dt = CONVERT(VARCHAR,CURRENT_TIMESTAMP,110) +'_'+ REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,108), ':', '-')
    SELECT @dt


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Yuan Saturday, January 17, 2015 3:48 AM
    • Marked as answer by tonofit Saturday, January 17, 2015 6:49 AM
    • Unmarked as answer by tonofit Saturday, January 17, 2015 6:49 AM
    • Marked as answer by tonofit Saturday, January 17, 2015 6:52 AM
    Saturday, January 17, 2015 3:44 AM

All replies

  • Try:

    'log_'+REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,101),'/','_')+'_'+CONVERT(VARCHAR,CURRENT_TIMESTAMP,108)

    Thursday, January 15, 2015 10:45 PM
  • I updated the question and it won't append the time correctly, Thanks for you response.
    Saturday, January 17, 2015 1:54 AM
  • The character':' is an illegal character in the file name. Try this:

    'log_'+REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,101),'/','_')+'_'+REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,108),':','_')

    • Marked as answer by tonofit Saturday, January 17, 2015 6:52 AM
    Saturday, January 17, 2015 3:06 AM
  • Since you added ":" to the file name, the file name is not valid. You can change ":" to "-".

    DECLARE @dt AS VARCHAR(50)
    SELECT @dt = CONVERT(VARCHAR,CURRENT_TIMESTAMP,110) +'_'+ REPLACE(CONVERT(VARCHAR,CURRENT_TIMESTAMP,108), ':', '-')
    SELECT @dt


    A Fan of SSIS, SSRS and SSAS


    • Edited by Guoxiong Yuan Saturday, January 17, 2015 3:48 AM
    • Marked as answer by tonofit Saturday, January 17, 2015 6:49 AM
    • Unmarked as answer by tonofit Saturday, January 17, 2015 6:49 AM
    • Marked as answer by tonofit Saturday, January 17, 2015 6:52 AM
    Saturday, January 17, 2015 3:44 AM
  • Couldn't use the : character for the time so used the _ and the name will look like this... log_01-16-2015_22_46_09
    Saturday, January 17, 2015 6:52 AM