locked
Want an sql query that show data from database which is currently being inserted in database RRS feed

  • Question

  • User1116376745 posted

    Want an sql query that show data from database which is currently being inserted in database i have used date created column also in which date is stored at the time of insertion

    Wednesday, July 18, 2018 12:35 PM

Answers

  • User753101303 posted

    Hi,

    Not sure what you mean with "currently". You are using the local db server date time utc or the local datetime ? With SQL Server it could be solmething such as :

    SELECT * FROM YourTable WHERE DateCreated>=DATEADD(hour,-1,GETUTCDATE()) ORDER BY DateCreated DESC -- Or GETDATE() ?

    which should show all rows inserted during the last hour starting with the very latest inserted rows.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 18, 2018 4:02 PM

All replies

  • User77042963 posted

    What data do you want to see? You table DDL and your process please.

    Wednesday, July 18, 2018 1:23 PM
  • User753101303 posted

    Hi,

    Not sure what you mean with "currently". You are using the local db server date time utc or the local datetime ? With SQL Server it could be solmething such as :

    SELECT * FROM YourTable WHERE DateCreated>=DATEADD(hour,-1,GETUTCDATE()) ORDER BY DateCreated DESC -- Or GETDATE() ?

    which should show all rows inserted during the last hour starting with the very latest inserted rows.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 18, 2018 4:02 PM
  • User1116376745 posted

    Not sure what you mean with "currently". You are using the local db server date time utc or the local datetime ? With SQL Server it could be solmething such as :

    Hi,

    Not sure what you mean with "currently". You are using the local db server date time utc or the local datetime ? With SQL Server it could be solmething such as :

    SELECT * FROM YourTable WHERE DateCreated>=DATEADD(hour,-1,GETUTCDATE()) ORDER BY DateCreated DESC -- Or GETDATE() ?

    which should show all rows inserted during the last hour starting with the very latest inserted rows.



    I mean to say that if i already had data in my table and now i am again inserting data in table then i should get the response of that data only which i inserted just now 

    Thursday, July 19, 2018 6:27 AM
  • User753101303 posted

    And you have a pk identity column ? It is done usually using https://docs.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-2017

    INSERT INTO MyTable(col1,col2) VALUES (@col1,@col2);SELECT * FROM MyTable WHERE pk=SCOPE_IDENTITY()

    insert a new row and then return all columns for this row (maybe additional columns with default values etc...) by using the value generated for the pk in  the WHERE clause (or if using Entity Framework it is done for you behind the scene).

    Thursday, July 19, 2018 7:06 AM
  • User1116376745 posted

    SELECT * FROM DelegateTable WHERE Date_Created>=DATEADD(second,-60,GETDATE()) ORDER BY Date_Created ASC

    Becoz my query is running with seconds that is why i used second 

    Above query worked for me in one sql server but when i am trying to run it in another sql server this doesnt work becoz another sql server system date is given in below format
     1.16 PM
    19-Jul-18

    And i want that this query should work in any sql server  on any system

    Thursday, July 19, 2018 7:48 AM
  • User753101303 posted

    It shouldn't care about the server configuration. Dates are always stored the same way. It just happen that the same value can be shown in many different ways depending on which convention is used.

    Or your DateCreated column is not a real DATETIME column ? Never use a varchar column to store date/times...

    Also it is REALLY not the right way to proceed unless you want all rows created in the last minute. If you ALWAYS want to reread the row you just inserted you should use a SCOPE_IDENTITY based solution (or give more details about your pk)

    Edit: be explicit about the error you have. Make sure about the type of the DateCreated column and if GETDATE or GETUTCDATE is used to populate this column.

    Thursday, July 19, 2018 8:10 AM
  • User1116376745 posted

    It shouldn't care about the server configuration. Dates are always stored the same way. It just happen that the same value can be shown in many different ways depending on which convention is used.

    Or your DateCreated column is not a real DATETIME column ? Never use a varchar column to store date/times...

    Also it is REALLY not the right way to proceed unless you want all rows created in the last minute. If you ALWAYS want to reread the row you just inserted you should use a SCOPE_IDENTITY based solution (or give more details about your pk)



    I have used datetime datatype for Date_Created column

    Thursday, July 19, 2018 8:12 AM
  • User753101303 posted

    And so the error you have on the other server is ? You can have an error when you convert a string to a date/time or when you convert a date/time to string you could not have the format you want.

    You should never have any problem when working only with "true" date/time values.

    Once again note that you have what you asked for ie currently you select all rows recently added in this time frame. You don't reread the row you just inserted even if in some cases (but not always) both could show the same result.

    Thursday, July 19, 2018 8:27 AM
  • User1116376745 posted

    There is no error giving on other server it is fetching all the data that is present in database. i am using datetime  datatype in sql as well as in Project i am using DateTime dataype in model class. In code i am inserting the value of Date_Created as DateTime.Now

    Thursday, July 19, 2018 8:36 AM
  • User753101303 posted

    It's suprising that you see ALL rows. You are sure values found in the Date_Created are correctly populated ? Check them as well as SELECT DATEADD(second,-60,GETDATE()). Once again how the value iust shown doesn't really matter. What matter  is that "actual" values  are correct.

    Then a more subtle problem could be that on one side you are using the local date/time on the web server and on the other site the local date/time of the web server but it shouldn't produce that effect (unless they are not in the same time zone or the clock is not correctly synchronized).

    Thursday, July 19, 2018 8:48 AM
  • User1116376745 posted

    It's suprising that you see ALL rows. You are sure values found in the Date_Created are correctly populated ? Check them as well as SELECT DATEADD(second,-60,GETDATE()). Once again how the value iust shown doesn't really matter. What matter  is that "actual" values  are correct.

    Then a more subtle problem could be that on one side you are using the local date/time on the web server and on the other site the local date/time of the web server but it should produce that effect (unless they are not in the same time zone or the clock is not correctly synchronized).



    Value stored in Date_Created is stored as below

    2018-07-19 14:25:48.723

    Also checked both server time zone the one in which query is working properly and the one in which query is not working both time zones are equal and in both Date_Created column is storing data as defined above

    Thursday, July 19, 2018 9:04 AM
  • User753101303 posted

    Then try maybe :

    SELECT Date_Created,DATEADD(second,-60,GETDATE()) FROM DelegateTable WHERE Date_Created>=DATEADD(second,-60,GETDATE()) ORDER BY Date_Created ASC

    to see if you can better understand the behavior you see? Or what if using -10 rather than -60 ? You have the same row count ? If you really see ALL rows I can only imagine that the Date_Created column is wrongly updated for all rows rather than really just for the newly inserted row ?????

    You could also try a quick demo below :

    DECLARE @t TABLE( pk INT IDENTITY NOT NULL,Date_created DATETIME NOT NULL)
    INSERT INTO @t VALUES (GETDATE())
    WAITFOR DELAY '0:0:2' -- Wait 2 s
    INSERT INTO @t VALUES (GETDATE())
    
    SELECT * FROM @t WHERE Date_created>DATEADD(second,-1,GETDATE()) -- 1 row shown
    SELECT * FROM @t WHERE pk=SCOPE_IDENTITY() -- if you really want the row you just inserted
    
    SELECT * FROM @t -- 2 rows

    Thursday, July 19, 2018 9:37 AM
  • User1116376745 posted

    Tried this query :- SELECT Date_Created,DATEADD(second,-1,GETDATE()) FROM DelegateTable 

    Below output is from the one sql server in which the query is not working. This server is of DUBAI so that is why DateADD function is giving output as in 2nd column.

    Date_Created                          (No column name)
    2018-07-19 14:25:48.723       2018-07-19 13:41:58.083
    2018-07-19 14:25:56.900       2018-07-19 13:41:58.083
    2018-07-19 14:26:22.267       2018-07-19 13:41:58.083
    2018-07-19 14:26:22.283       2018-07-19 13:41:58.083
    2018-07-19 14:34:51.280       2018-07-19 13:41:58.083
    2018-07-19 14:34:51.287       2018-07-19 13:41:58.083


    Below output is from the one sql server in which the query is working

    Date_Created                        (No column name)
    2018-07-19 14:23:09.043       2018-07-19 15:33:42.463
    2018-07-19 14:23:22.763       2018-07-19 15:33:42.463
    2018-07-19 14:24:19.010       2018-07-19 15:33:42.463
    2018-07-19 14:24:19.907       2018-07-19 15:33:42.463

    i just have run the query on both server and the output on both sql is different


    Can you please tell me how i should insert system datetime using code like i used in my project Date_Created = DateTime.Now; to insert date

    Thursday, July 19, 2018 9:45 AM
  • User753101303 posted

    sana_khan

    the one in which query is not working both time zones are equal

    As Date_Created is close on both servers but GETDATE differs by 2 hours I believe it is wrong. What if you try to use DateTime.UtcNow on the web server and GETUTCDATE() on the SQL Server side to get consistent values ?

    Edit: you could also try SELECT SYSDATETIMEOFFSET() which should allow to make 100% sure this is really a time zone issue but anyw ay using UTC is likely best anyway (wanted to  just to fix what appeared to be an unrelated issue first).

    And for the last time, please don't consider this approach is supposed to be reliable if you really want to read back the row you just inserted. I just didn't understood what you wanted ("recently" rather than "the row I just inserted"). It just shows "recently inserted" rowS

    If you REALLY want to read the same row that you just inserted, you should select the row based on its primary key (you could also use https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017 if SCOPE_IDENTITY doesn't fit your need)

    A quick demo :

    DECLARE @t TABLE( pk UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID(),Date_created DATETIME NOT NULL)
    INSERT INTO @t(Date_created) OUTPUT inserted.* VALUES (GETDATE()) -- returns the newly inserted row 
    INSERT INTO @t(Date_created) OUTPUT inserted.* VALUES (GETDATE()) -- returns the newly inserted row 
    INSERT INTO @t(Date_created) OUTPUT inserted.* VALUES (GETDATE()) -- returns the newly inserted row 
    SELECT * FROM @t -- all rows note that the Date_Created have the same value as they were inserted in close succession
    

    Thursday, July 19, 2018 11:02 AM