I'm trying to use temporal tables for our data lake environment to track changing data over time.  The first hurdle I hit is loading historical data to seed the tables.  Everything is loading as "now" instead of as of the point in the past.  Searching online the only solution I have found is to turn off system versioning, disable the period in the table, make changes to the table to fix the dates, then turn system versioning back on and hope that SQL Server will let me.  Is there a better way to force the datetime values to be what I need them to be? Also, going forward, we are loading data that was updated yesterday and captured last night and being loaded into the lake early this morning.  I need to use yesterday's date as the effective date for the temporal table effective date, not the current timestamp.  Same issue, how can I force the datetime values?
121357
We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables.  For the BeginDate, we want it to be dependent on transaction date and not the current getdate time. We are reprocessing a history of transactions. Example below, customer has gym or bank status, and goes from inactive, to active, or pending, dependent on transaction date.  We currently have this.  CREATE TABLE dbo.Department (     CustomerId int primary key,     MembershipStatus int,     TransactionDate datetime  );   We we would like to create a table like this.  CREATE TABLE dbo.DepartmentHistory (     CustomerId int primary key,     MembershipStatus int,     TransactionDate datetime,     BeginDatetime datetime,     EndDatettime datetime  );   Example usage would be:   first Customer Transaction on 3/5/2018 as Pending P        +------------+--------+-----------+---------+     | CustomerId | Status | BeginDate | EndDate |     +------------+--------+-----------+---------+     | 1          | P      | 3/5/2018  | NULL    |     +------------+--------+-----------+---------+    second transaction is 4/21/2018 is Active A        +------------+--------+-----------+-----------+     | CustomerId | Status | BeginDate | EndDate   |     +------------+--------+-----------+-----------+     | 1          | P      | 3/5/2018  | 4/21/2018 |     | 1          | A      | 4/21/2018 | NULL      |     +------------+--------+-----------+-----------+
1060
I've been noticing something like this popping up every 15 seconds in my server logs:  2020-01-30 21:10:30 UTC::@:[24969]:ERROR: XX000: cache lookup failed for type 0 2020-01-30 21:10:30 UTC::@:[24969]:CONTEXT: automatic vacuum of table "myschema.mytable" 2020-01-30 21:10:30 UTC::@:[24969]:LOCATION: get_typlenbyval, lsyscache.c:2036   When I manually run a VACUUM ANALYZE myschema.mytable; it runs without any error, and the error in my logs goes away. However, inevitably, it returns again at some point.   The table in question is defined as follows:  tfs_dev=&gt; \d myschema.mytable;                                                    Table "myschema.mytable"    Column    |   Type    | Collation | Nullable |                                  Default -------------+-----------+-----------+----------+---------------------------------------------------------------------------  level       | text      |           | not null |  id          | integer   |           | not null | nextval('myschema.mytable_id_seq'::regclass)  text_id     | text      |           |          |  name        | text      |           |          |  geom        | geometry  |           |          |  valid_dates | tstzrange |           |          | tstzrange(NULL::timestamp with time zone, NULL::timestamp with time zone)  adjacent    | integer[] |           |          |  valence     | integer   |           |          |  color       | smallint  |           |          | 0 Indexes:     "mytable_pkey" PRIMARY KEY, btree (level, id)     "mytable_text_id_key" UNIQUE CONSTRAINT, btree (text_id)     "mytable_gix" gist (level, geom, id, text_id, name)     "mytable_spgix" spgist (geom)     "mytable_text_id_ix" btree (text_id, level, id, name) Referenced by:     TABLE "otherschema.othertable" CONSTRAINT "othertable_mytable_fk" FOREIGN KEY (level, level_id) REFERENCES myschema.mytable(level, id)     TABLE "otherschema.othertable2" CONSTRAINT "othertable2_mytable_fk" FOREIGN KEY (level, level_id) REFERENCES myschema.mytable(level, id)   Many other posts with a similar error seem to be about a foreign data wrapper, which is present in this database, but not with this table/schema.
58756
I am interested in determining the ages of all 70+ my servers. I would like to create a growth graph showing when servers and databases were added to the environment either/both monthly or annually. I executed the following query using the SSMS multi-query feature with only partial success.  SELECT [name], create_date FROM sys.databases   It's partial success because the master and model databases return with a date of 'April 8, 2003' when I know that I created some of these servers during the past couple years.  I refresh databases from one server to another several times a week, which appears to be causing issues with the database create date.   Is there a simple method to determine the actual create date for both servers and databases?
59158
It seems to be quite difficult to find comparisons between system-versioned temporal tables and the older options, such as DB triggers and CDC. I currently don't have the time to write an extended test on SQL Server 2016, so I thought I'd ask about it here.  Basically, the typical advantage with triggers is that they are easier to manage in stand-alone and clustered / alwaysOn environments, are real-time for being synchronized, and have access to session data such as the user ID.  CDC on the other hand while requiring a bit more management and being asynchronous, is much lighter, and thus performs far better. So if there's any doubt at all that the bottle necking caused by triggers could become a problem, CDC will basically be the superior solution. In terms hardware requirements, there's a negligible extra space requirement by CDC due to using logs and cdc audit tables for tracking the changes.  The question: How do temporal tables compare to the two above? In terms of speed, performance, storage space usage. WHEN should I use temporal tables instead of triggers or CDC? When should I not?  I understand anything as potentially complex as the business requirements and technical limitations behind DB auditing isn't going to have one easy answer, as it depends largely on the requirements and scope of the project. But anything to shed more light on the questions above would be appreciated. Thanks!
87714
today I used the system object sys.dm_tran_active_transactions (to be honest for the first time). I had an issue with distributed transactions (Microsoft DTC) and went digging there after I had no clue with the sp_whoIsActive oputput alone.This issue is solved but now I see that there are quite old transactions in the table with type "worktable". Here's the output for the following query  SELECT  *,  case transaction_type          when 1 then 'Read/Write'          when 2 then 'Read-Only'           when 3 then 'System'          when 4 then 'Distributed'         else 'Unknown - ' + convert(varchar(20), transaction_type)       end as tranType,      case transaction_state        when 0 then 'Uninitialized'        when 1 then 'Not Yet Started'        when 2 then 'Active'        when 3 then 'Ended (Read-Only)'        when 4 then 'Committing'        when 5 then 'Prepared'        when 6 then 'Committed'        when 7 then 'Rolling Back'        when 8 then 'Rolled Back'        else 'Unknown - ' + convert(varchar(20), transaction_state)   end as tranState,   case dtc_state        when 0 then NULL        when 1 then 'Active'        when 2 then 'Prepared'        when 3 then 'Committed'        when 4 then 'Aborted'        when 5 then 'Recovered'        else 'Unknown - ' + convert(varchar(20), dtc_state)   end as dtcState FROM    sys.dm_tran_active_transactions  ORDER BY transaction_begin_time     The transaction_begin_time happens to correlate with the time the SQL Server Service was last restarted.  Should I be concerned about this in any way? I did have a look at  to learn more about the dmv. However it just mentions that worktables are used for storing temporary query results in tempdb. However my understanding is that if I kick off a query whose execution plan decides to spool to tempdb and use a worktable a new worktable is created each time it is needed. Therefore it seems odd that therer are 6 worktables so old.  I could hypothesize that these are tables reused since service restart for monitoring queries which run constantly or internal SQL server stuff. Anyway the transaction type read only seems to be harmless and I noticed no sign of blocking or version store growth.  Thanks in advance for your assistance  Martin
13230
I would like to think that some of the software I'm writing today will be used in 30 years. But I am also aware that a lot of it is based upon the UNIX tradition of exposing time as the number of seconds since 1970.  #include &lt;stdio.h&gt; #include &lt;time.h&gt; #include &lt;limits.h&gt;  void print(time_t rt) {     struct tm * t = gmtime(&amp;rt);     puts(asctime(t)); }  int main() {     print(0);     print(time(0));     print(LONG_MAX);     print(LONG_MAX+1); }   Execution results in:   Thu Jan  1 00:00:00 1970 Sat Aug 30 18:37:08 2008 Tue Jan 19 03:14:07 2038 Fri Dec 13 20:45:52 1901           The functions ctime(), gmtime(), and localtime() all take as an argument a time value representing the time in seconds since the Epoch (00:00:00 UTC, January 1, 1970; see time(3) ).      I wonder if there is anything proactive to do in this area as a programmer, or are we to trust that all software systems (aka Operating Systems) will some how be magically upgraded in the future?  Update It would seem that indeed 64-bit systems are safe from this:  import java.util.*;  class TimeTest {     public static void main(String[] args) {         print(0);         print(System.currentTimeMillis());         print(Long.MAX_VALUE);         print(Long.MAX_VALUE + 1);     }      static void print(long l) {         System.out.println(new Date(l));     } }    Wed Dec 31 16:00:00 PST 1969 Sat Aug 30 12:02:40 PDT 2008 Sat Aug 16 23:12:55 PST 292278994 Sun Dec 02 08:47:04 PST 292269055   But what about the year 292278994?
112994
I have a balances table in PostgreSQL 9.3 that looks like this:  CREATE TABLE balances (   user_id INT , balance INT , as_of_date DATE );  INSERT INTO balances (user_id, balance, as_of_date) VALUES   (1, 100, '2016-01-03') , (1,  50, '2016-01-02') , (1,  10, '2016-01-01') , (2, 200, '2016-01-01') , (3,  30, '2016-01-03');   It only contains balances for dates that a user has made a transaction. I need it to contain a row for each user with their balance on each date in a given date range.   If the user does not have a row for a given date in the range, I need to use their balance from the day before. If the user created their account after a given date in the range, I need to avoid creating a row for that user/date combination.   I can reference an accounts table to get users' create_date:  CREATE TABLE accounts (   user_id INT , create_date DATE );  INSERT INTO accounts (user_id, create_date) VALUES   (1, '2015-12-01') , (2, '2015-12-31') , (3, '2016-01-03');   My desired result looks like this:  +---------+---------+--------------------------+ | user_id | balance |        as_of_date        | +---------+---------+--------------------------+ |       1 |     100 | 2016-01-03T00:00:00.000Z | |       1 |      50 | 2016-01-02T00:00:00.000Z | |       1 |      10 | 2016-01-01T00:00:00.000Z | |       2 |     200 | 2016-01-03T00:00:00.000Z | |       2 |     200 | 2016-01-02T00:00:00.000Z | |       2 |     200 | 2016-01-01T00:00:00.000Z | |       3 |      30 | 2016-01-03T00:00:00.000Z | +---------+---------+--------------------------+   Note that rows have been added for user 2 for 2016-01-02 and 2016-01-03, carrying over the previous balance from 2016-01-01; and that no rows have been added for user 3, who was created on 2016-01-03.  To generate a series of dates in a date range, I know I can use:  SELECT d.date FROM GENERATE_SERIES('2016-01-01', '2016-01-03', '1 day'::INTERVAL) d   ...but I am struggling with LEFT JOINing that series with each set of rows grouped by user_id.
95996
My manager is requesting to know when was the last time a number of reports were last ran to see if they are getting used. He requested info from our vendor and the vendor if coming to me for SA level permission to dig through the relative SQL Server.   Obviously, I won't be doing that but I would like to know is there a way to track the last time a specific query has been executed?  This database resides on an old SQL Server 2008 R2 install so I am sure my options are limited if not non-existent especially since he wants to look back as far as 18 months.   Just figured I would ask in case there might be at least a partial way to go back in history and track this information.
81419
Is it possible to use the =today() function (or something else) to insert the current date but then not update each time the file is opened?  I want to create a numbers template for my invoices. I'd like it to auto fill invoice date and date due then, when I save the document, store the values, not the function.
11639
In the footer of my page, I would like to add something like "last updated the xx/xx/200x" with this date being the last time a certain mySQL table has been updated.  What is the best way to do that?  Is there a function to retrieve the last updated date? Should I access to the database every time I need this value?
26494
Hi everyone and thanks for your help. I have the following situation: a table called statements that contains fields id(int), stmnt_date(date), debit(double), credit(double) and balance(double)   I want to calculate the balance following these rules:   The first row balance (chronologically) = debit - credit and for the rest of the rows  current row balance = chronologically previous row balance + current row debit - current row credit  As you can see on the picture above the rows are not arranged by date and that's why I used the word chronologically twice to emphasize on the importance of the stmnt_date value.  Thank you very much for your help.
5162
Using SQL Server - which is the fastest or best practice method to use for date retrieval? Is there a difference?
85445
I was querying data from  using a LINQ query in LINQPAD and found that the data I got back is quite obsolete. I searched for Shekhar-Pro (me) by using a where clause on ID 399722 and the data returned was for 8 Jan 2011. Why was the data so old? Isn't the data updated in real time, or at least frequently? If not, where can I get realtime data?
41793
I have a child table that is something like this:  [Cust Date Table]  | Customer ID | Some Date  | Balance | +-------------+------------+---------+ |           1 | 2012-04-30 |   20.00 | |           1 | 2012-03-31 |   50.00 | |           2 | 2012-04-30 |    0.00 | |           2 | 2012-03-31 |   10.00 |  |           3 | 2012-03-31 |   60.00 | |           3 | 2012-02-29 |   10.00 |   I would like to be able to get a result set like this - one record for each client with the latest date:  | Customer ID | Some Date  | Balance | +-------------+------------+---------+ |           1 | 2012-04-30 |   20.00 |  |           2 | 2012-04-30 |    0.00 | |           3 | 2012-03-31 |   60.00 |   I know that I can do this for each individual "Customer ID" with the following SQL (SQL Server syntax):  select top 1  [Some Date], [Customer ID], [Balance] from [Cust Date Table] where [Customer ID] = 2 order by [Some Date] desc   | Customer ID | Some Date  | Balance | +-------------+------------+---------+ |           2 | 2012-04-30 |    0.00 |   But I'm not sure how to get all three of the records I want.  I'm not sure if this is a situation that calls for a sub-query or something else.    Please note that the max date can be different for any given [Customer ID], (in this example, customer 3's maximum date is 2012-03-31 whereas the other records have a max date of 2012-04-30). I have tried  select [Customer ID], MAX([Some Date]) AS [Latest Date], Balance  from [Cust Date Table]  group by [Customer ID], Balance;    The problem is this doesn't return just the one row for each customer - it returns multiple rows.
80348
The detail view of Thunar shows the modification time as localized date (dd.mm.YYYY for my location), which is fine, but I would like to see the time, too.   And it doesn't show the recently modified files in the same manner, butnew years new years  decorates them as 'yesterday, today, monday' and so on, which is a nice programming etude, but not really useful to me.   Au contraire - it is pretty annoying to have this mixture in the interface. The next version will look into my calender and mark some files 'easter 2007, moms birthday 2002' and 'third monday of last month'. Thank you!   But if ohters prefer it - I'm fine. I just want to know whether I can modify the setting somehow. This is Xubuntu 12.04 LTS, Thunar 1.2.3.
91913
is it possible to skip immediately preceding time points? So that, if, for example, you are using model order 2, that is, two time points, but not the two immediately previous time points, but rather, say for example, skipping 2 points, and then using those two points.  So, e.g., it would be y=x(t-3) + x(t-4).
95317
We have a setup where the production SQL Server databases are replicated to an offsite location using SQL Server Replication (transactional replication).  I am looking at using  to get the updated data in order to perform some processing and add values to a cache. Before I start down this road, does anyone know if it's possible to use Change Tracking on a replication target? It seems like it should work, but it also seems complex enough that there would be gotchas.