Error in update statement

  • Hello experts,

    I’m having a little challenge in updating one of my table. I’m 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 don’t 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.

  • mr_adeelalisyed (12/10/2009)


    Hello experts,

    I’m having a little challenge in updating one of my table. I’m 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 don’t 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.

    Remove the Select keyword before convert and try


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • Thanks a lot Bru, it worked. Now one more question. Since I just need date in 'dd/mm/yyyy' format should i use varchar(10) instead of smalldatetime?

  • Where are you using varchar(10) and smalldatetime, I dont see the DDL stmts.

    But yes, you can use Varchar(10) to store in the format that you wanted to.


    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply