none
Update table Date ? RRS feed

  • Question

  • Update TableName set time = date

    what can i use in date ?

    to add my current server date ?

    • Moved by Tom PhillipsModerator Monday, August 23, 2010 2:55 PM TSQL Question (From:SQL Server Database Engine)
    Saturday, August 21, 2010 10:42 AM

Answers

  • Should have asked in T SQL forum


    GETDATE() or CURRENT_TIMESTAMP will give your current date and time on server

    If you only need date then use

     SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) OR

    SELECT CONVERT(VARCHAR(10),GETDATE(),101)

    So your update statement may look like

    UPDATE <table>
    SET <col> = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
    • Marked as answer by abu5alilo Sunday, August 22, 2010 7:09 AM
    Saturday, August 21, 2010 11:16 AM
  • To add on to Chirag's response, I suggest that you avoid implicit conversion from the SQL Server internal storage format and specify an explict base date for the DATEADD/DATEDIFF functions:

    DATEADD(day, '19000101', DATEDIFF(dat, '19000101', GETDATE()));
    
    Also, see Tibor's excellent article on date/time data (http://www.karaszi.com/SQLServer/info_datetime.asp).
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by abu5alilo Sunday, August 22, 2010 7:09 AM
    Saturday, August 21, 2010 2:47 PM

All replies

  • Should have asked in T SQL forum


    GETDATE() or CURRENT_TIMESTAMP will give your current date and time on server

    If you only need date then use

     SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) OR

    SELECT CONVERT(VARCHAR(10),GETDATE(),101)

    So your update statement may look like

    UPDATE <table>
    SET <col> = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
    • Marked as answer by abu5alilo Sunday, August 22, 2010 7:09 AM
    Saturday, August 21, 2010 11:16 AM
  • To add on to Chirag's response, I suggest that you avoid implicit conversion from the SQL Server internal storage format and specify an explict base date for the DATEADD/DATEDIFF functions:

    DATEADD(day, '19000101', DATEDIFF(dat, '19000101', GETDATE()));
    
    Also, see Tibor's excellent article on date/time data (http://www.karaszi.com/SQLServer/info_datetime.asp).
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Marked as answer by abu5alilo Sunday, August 22, 2010 7:09 AM
    Saturday, August 21, 2010 2:47 PM