A View with DateTime it was run - how do I put Now (date time) as default in a column

Question

A View with DateTime it was run - how do I put Now (date time) as default in a column

Mile Higher Than Sea Level

SSCarpal Tunnel

Points: 4828
More actions
December 3, 2012 at 10:35 am

#285715

In MS Access - a query can have a column defined as MyTimeStamp: NOW()
When the Query is run for a MakeTable or Select, the column MyTimeStamp would have the Date/Time the query was run.
For SQL Server 2008r2, an existing View is available for another dept to import data.
It would be desirable to have a MyTimeStamp with the time/date the View was run.
Just need a time stamp in the column that is updated at the execution of the View.
How can this be accomplished.
Data sets are always between 1,000 and 100,000 records in this case.
Thanks

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Mile Higher Than Sea Level (12/3/2012)
In MS Access - a query can have a column defined as MyTimeStamp: NOW()
When the Query is run for a MakeTable or Select, the column MyTimeStamp would have the Date/Time the query was run.
For SQL Server 2008r2, an existing View is available for another dept to import data.
It would be desirable to have a MyTimeStamp with the time/date the View was run.
Just need a time stamp in the column that is updated at the execution of the View.
How can this be accomplished.
Data sets are always between 1,000 and 100,000 records in this case.
Thanks

Add getdate() as a new column??? Not really sure what you mean by a column that is updated when you execute a view.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 2

Views aren't something you execute. Do you mean when you query data from one?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mile Higher Than Sea Level SSCarpal Tunnel Points: 4828 More actions · Answer 3

Sorry about the cross up in terms. In Access code, we can Execute a query (of a SQL View).

I figured it out with some trial and error, heavily weighted on the latter of the two.

In the design view of SSIS:

In the Column - enter GETDATE()

in the Alias enter the column name e.g. DateDataPulled

In the View def - Select (other field names), GETDATE() AS DateDataPulled

That creates an equalivant to the MS Access SQL NOW() function

This view was created with a user name for a different division to pull data from my database.

The user can only view three views and nothing else from my database.

When the user pulls data (used to update the GIS data) the DateDataPulled will provide a record-level date.

Thank you all for your help. Those of us who only use SQL as 10% of our task really appreicate your contribution.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Mile Higher Than Sea Level (12/3/2012)
Sorry about the cross up in terms. In Access code, we can Execute a query (of a SQL View).
I figured it out with some trial and error, heavily weighted on the latter of the two.
In the design view of SSIS:
In the Column - enter GETDATE()
in the Alias enter the column name e.g. DateDataPulled
In the View def - Select (other field names), GETDATE() AS DateDataPulled
That creates an equalivant to the MS Access SQL NOW() function
This view was created with a user name for a different division to pull data from my database.
The user can only view three views and nothing else from my database.
When the user pulls data (used to update the GIS data) the DateDataPulled will provide a record-level date.
Thank you all for your help. Those of us who only use SQL as 10% of our task really appreicate your contribution.

You are welcome. I thought that may be what you needed but wasn't sure. Glad you got it sorted out. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/