last extracted date and change date

Question

last extracted date and change date

Shambhu Rai 1,411

Hi Expert,

Have loaded data in table for 2 times in a week without date dimension table or last extracted date ... how will i identify last refresh date and current refresh date in sql server

create table emp(employee varchar(23),Age varchar(23),Salary varchar(23))

insert into emp('Test1','23','10')

insert into emp('Test2','25','20')

insert into emp('Tes3','27','30')

User's image

Raksha Chourasia 75 Reputation points

2023-09-18T04:53:19.9433333+00:00

To identify the last refresh date you can check with a query on it.

SELECT name, modify_date

FROM sys.tables

WHERE name = 'emp';

This will help to modify date

For identifying the current refresh date :

You can use the current date and time from the GETDATE() function:

SELECT GETDATE() AS CurrentRefreshDate;

Both codes will help you to do this.
Anonymous

2023-09-18T09:24:48.3966667+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.
Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator

2023-09-18T20:30:34.1+00:00

To identify the last refresh date you can check with a query on it. SELECT name, modify_date FROM sys.tables

Sorry, this is plain wrong. modify_date gives you the most recent schema change on the table, but it says nothing about when data was changed.

1 answer

Your answer

Raksha Chourasia 75 Reputation points

2023-09-18T04:53:19.9433333+00:00

To identify the last refresh date you can check with a query on it.

SELECT name, modify_date

FROM sys.tables

WHERE name = 'emp';

This will help to modify date

For identifying the current refresh date :

You can use the current date and time from the GETDATE() function:

SELECT GETDATE() AS CurrentRefreshDate;

Both codes will help you to do this.
Anonymous

2023-09-18T09:24:48.3966667+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.
Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator

2023-09-18T20:30:34.1+00:00

To identify the last refresh date you can check with a query on it. SELECT name, modify_date FROM sys.tables

Sorry, this is plain wrong. modify_date gives you the most recent schema change on the table, but it says nothing about when data was changed.

Answer 1

Erland Sommarskog 133.6K MVP Volunteer Moderator

If that is all the data you have, the answer is you don't. You will need to arrange for this by adding a datetime column that defaults to sysdatetime() or similar.

Shambhu Rai 1,411 Reputation points

2023-09-15T21:42:23.6033333+00:00

I heard about something called changed table ... how about that
Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator

2023-09-15T21:46:26.9733333+00:00

There is a function CHANGETABLE which is part of Change Tracking. But Change Tracking only tracks that something has changed, not when.

You may be thinking of temporal tables. With a temporal tables, updated and deleted rows are automatically saved in a history table, and there is specific syntax so you can query how the data was at a certain point in time. Whether that fits your scenario, I don't know. Your question is a bit vague.
Shambhu Rai 1,411 Reputation points

2023-09-15T21:53:45.3533333+00:00

sure can you give me an example for this on my query please CHANGETABLE
Erland Sommarskog 133.6K Reputation points MVP Volunteer Moderator

2023-09-15T21:56:56.5466667+00:00

To be able to use CHANGETABLE, you first need to configure Change Tracking, and I can't see that it is applicable to your case. If you want to learn about the function, mr Google can help you. Which is where I would need to go, if I would have to use it myself; I don't know it by heart. So you may just try yourself.
Shambhu Rai 1,411 Reputation points

2023-09-15T22:25:50.03+00:00

Any other suggestion from other experts on CHANGETABLE please

Share via

last extracted date and change date

1 answer

Your answer