Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009
    Posts
    28

    Unanswered: Error in update statement

    Hello experts,

    Im having a little challenge in updating one of my table. Im trying to capture create_date from sys.databases. when I try to update my table using the following code it
    works fine no problems.

    update #TempTotalDatabase
    set DBCreationDate = create_date
    from sys.databases
    WHERE DBName = name

    However create_date field gives me Date with time. I dont need time. So I figured out a covert function which give me only date. Here is the code for that

    select convert (varchar(10), create_date, 103) from sys.databases

    I exactly need create_date in the above format. Now when I tried to update my table with the following code I end up having error. Here is the code and error.

    update #TempTotalDatabase
    set DBCreationDate = select convert (varchar(10), create_date, 103)
    from sys.databases
    WHERE DBName = name

    ERROR:
    Msg 156, Level 15, State 1, Line 2
    Incorrect syntax near the keyword 'select'.

    Can somebody correct my mistake please?

    Thanks a lot in advance.
    Error in update statement

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT GetDate() As date_and_time
         , DateAdd(dd, DateDiff(dd, 0, GetDate()), 0) As date_only
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •