locked
How to set value for column RRS feed

  • Question

  • User900423697 posted

    Hi all, I have a column name file_name in my table. There are multiple rows with different (unique) file name in the table. for example, 

    employee.txt

    providers.txt

    attributes.txt

    When I run job, new txt file will be generated in a folder with same file name. My requirement is to append current date at the end of the file name. For ex - employee_09_09_2020.txt but I'm not allowed to make any change in the job. I can only update the column (file_name) values. How is it possible to set a value in column where the filename will have current date appended?

    When I run select file_name from dbo.config then the file_name should return file_name appened with current date. I can't make any update in the select query.  

    Wednesday, September 9, 2020 7:57 PM

Answers

  • User-939850651 posted

    Hi rangasamy_tv,

    According to what you said before, you cannot modify the select statement, so if you want to get the date attached to the field, I think you can only achieve your requirement from the update statement.

    You could try to use Sql Server Job, it should meet your requirements.

    Something like this:

    Result:

    Note: I set the job to be executed every 10 seconds for testing, please make the corresponding changes according to your requirements.

    Hope this can help you.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 11, 2020 6:44 AM

All replies

  • User-939850651 posted

    Hi rangasamy_tv,

    If you want to add the current date to the end of the file name, you could try something like this:

    create table [dbo].config(
    	file_name varchar(255)
    )
    
    insert into [dbo].config values ('employee.txt'),
    ('providers.txt'),('attributes.txt')
    
    -- update records without date
    update dbo.config set file_name = CONCAT(
    		SUBSTRING(file_name,1,CHARINDEX('.txt',file_name)-1),'_',FORMAT (GETDATE(), 'dd_MM_yyyy'),'.txt')
    		where CHARINDEX('_',file_name)=0
    
    --update all records
    update dbo.config set file_name = CONCAT(
    		SUBSTRING(file_name,1,
    		IIF(CHARINDEX('_',file_name)!=0,CHARINDEX('.txt',file_name)-12,CHARINDEX('.txt',file_name)-1)
    		),'_',FORMAT (GETDATE(), 'dd_MM_yyyy'),'.txt')
    
    select file_name from config

    Result:

    Do you need to modify it every day? If so, I think that is not a good design.

    If I misunderstood something, could you express your requirements more clearly?

    Best regards,

    Xudong Peng

    Thursday, September 10, 2020 7:53 AM
  • User900423697 posted

    Thanks Xudong Peng for the response. 

    Yes I will generating files everyday and I can't execute update statement everyday before I generate files. I'm expecting something generic without executing update statement. Whenever I select file_name column then it should always return with current date appended. Please let me know if this info helps you.

    Thanks.

    Thursday, September 10, 2020 4:13 PM
  • User-939850651 posted

    Hi rangasamy_tv,

    According to what you said before, you cannot modify the select statement, so if you want to get the date attached to the field, I think you can only achieve your requirement from the update statement.

    You could try to use Sql Server Job, it should meet your requirements.

    Something like this:

    Result:

    Note: I set the job to be executed every 10 seconds for testing, please make the corresponding changes according to your requirements.

    Hope this can help you.

    Best regards,

    Xudong Peng

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 11, 2020 6:44 AM
  • User900423697 posted

    Thank you so much XuDong Peng. It helps me.

    Friday, September 11, 2020 9:28 PM