How to set value for column

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.  

Answer 1

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

Answer 2

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.

Answer 3

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

Answer 4

User900423697 posted

Thank you so much XuDong Peng. It helps me.