SQL Server Performance

currentdate in a table to show getdate

Discussion in 'General Developer Questions' started by aaronsandy, Jul 27, 2005.

  1. aaronsandy New Member

    I have a coloumn currentdate in a table.I want it to show todays date eachtime I select the current date.Is this is possible.
  2. Madhivanan Moderator

    Is this?

    Select othercolumns,getdate() as CurrentDate from yourTable

    If not, post some sample and expected result


    Madhivanan

    Failing to plan is Planning to fail
  3. aaronsandy New Member

    no,what I am looking for is

    select currentdate from mytable

    result should be todays date

    As per my knowledge its not possible,But management wants something like this
  4. Madhivanan Moderator

    I asked you to give some sample and expected result


    Madhivanan

    Failing to plan is Planning to fail
  5. aaronsandy New Member

    Here is structure of mytbl

    frstname|lastnaem|add|phone|space|expiration|currentdate|status
    xxx |yyy |zzz|77777|900| |07/10/2005|07/25/2005 |

    Now if I do
    select * from mytbl

    the result set should be
    frstname|lastnaem|add|phone|space|expiration|currentdate|status
    xxx |yyy |zzz|77777|900| |07/10/2005|07/27/2005 |



    Is there is any method which could make this possible


  6. Madhivanan Moderator

    Select frstname,lastnaem,add,phone,space,expiration,Getdate() as currentdate,status
    from mytbl



    Madhivanan

    Failing to plan is Planning to fail
  7. Adriaan New Member

    Yes, but the "currentdate" column in his table won't show the current date. You could create a "calculated column" to show the current date, but to be honest that would be silly - just include the expression in your query. Better still, why not make the client app show the system date from the client computer?

    BTW, did someone take over aaronsandy's login a few days ago?
  8. FrankKalis Moderator

  9. aaronsandy New Member

    What I want is this Getdate() value should be updated in currentdate field simultaneously
  10. FrankKalis Moderator

    quote:
    BTW, did someone take over aaronsandy's login a few days ago?
    Why are you asking this?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  11. Madhivanan Moderator

    >>What I want is this Getdate() value should be updated in currentdate field simultaneously

    Then no need for seperate column
    My first reply will do that


    Madhivanan

    Failing to plan is Planning to fail
  12. Adriaan New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by FrankKalis</i><br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />BTW, did someone take over aaronsandy's login a few days ago?<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Why are you asking this?<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><font size="1">Perhaps I'm confusing two logins, but I seem to remember that there was a new forum member a few months ago whose earliest postings were slightly annoying: asking for info that could easily be found in BOL, questions that looked like homework assignments ... but after a number of comments the quality of this member's postings improved a lot.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />And now we're again seeing a lot of homework assignment-style postings ... but as I said, I could be confusing two different forum members.</font id="size1"><br /><br />To return to the original question, is the idea to record the last time the record was updated? Only way to do that is to update your "currentdate" field in an update trigger.<br /><br />Actually, I think it would be an excellent extension to SQL Server's functionality if we could have some sort of an AUTOUPDATE property for table columns, similar to a DEFAULT constraint which is of course coupled to the insert event, but now coupled to the update event. It would save a lot of work writing all those triggers ...
  13. Madhivanan Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">Perhaps I'm confusing two logins, but I seem to remember that there was a new forum member a few months ago whose earliest postings were slightly annoying: asking for info that could easily be found in BOL, questions that looked like homework assignments ... but after a number of comments the quality of this member's postings improved a lot.<br /><br />And now we're again seeing a lot of homework assignment-style postings ... but as I said, I could be confusing two different forum members.<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Correct [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  14. FrankKalis Moderator

    quote:
    Perhaps I'm confusing two logins, but I seem to remember that there was a new forum member a few months ago whose earliest postings were slightly annoying: asking for info that could easily be found in BOL, questions that looked like homework assignments ... but after a number of comments the quality of this member's postings improved a lot.

    And now we're again seeing a lot of homework assignment-style postings ... but as I said, I could be confusing two different forum members.
    The loginname is unique in Snitz forum. I am not aware of a way to have duplicates here.
    I also remember that time and, without being combative or offending, aaronsandy should really get hands on the tremendous wisdom that is to discover in the BOL. Most of his questions are indeed FAQs that could be answered by a quick search in BOL and/or online communities.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  15. Madhivanan Moderator

    and dont forget to notice that he does not give proper information in his questions although he crossed more than 300 posts [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  16. aaronsandy New Member

    Here is what I want
    I am looking for a provison or method which will automatically change the currentdate field of my table to systems date everyday.No other fields will be updated.
  17. Madhivanan Moderator

    If this is your actual requirement then there is no need of having seperate column. Just do the query as I specified in my first reply


    Madhivanan

    Failing to plan is Planning to fail
  18. aaronsandy New Member

    I dont need to select.I was just giving ex that if i use select..There is no front end as such where I need to write "select othercoloumns,getdate() as currentdate from mytable" ,My requirements is different.What I want is the table should be updated with currentdate everyday.

  19. FrankKalis Moderator

    quote:
    What I want is the table should be updated with currentdate everyday.
    Create a job that runs once a day and updates the table.
    But may I add, that I see no valid reason to store such redundant data?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  20. Twan New Member

    another option might be to create a view which selects all of the columns from the table plus one column called currentdate which is set to getdate()

    You may even be able to make this transparent by renaming your current table, and giving the view the name of the table

    Cheers
    Twan
  21. ghemant Moderator

    Hi,
    as Twan said you could set the getdate() as select coulumn in a view or a frankkalis's reply suggest do a schedule job run... or just run the query as Madhivanan's post.

    hsGoswami
    ghemant@gmail.com
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami
  22. Adriaan New Member

    Add a calculated field, like so:
    create table tmp (ID INT PRIMARY KEY, CurrentDate AS CONVERT(VARCHAR(10), GETDATE(), 120) )

    insert into tmp values (1)

    select * from tmp

    drop table tmp

Share This Page