Post reply

Adding a formula to Computed Column Specification formula in SQL Server 2008

  • September 30, 2019 at 10:45 am

    #3684503

    I am trying to add the following query to the formula property to  calculate minutes to hours.

    (CONVERT(char(5), DATEADD(MINUTE, convert(int,[sun_total])), '19000101'), 108) as MinuteToHour)

    If i run it using a Select statement t it works fine but when I add it to the formula box without the SELECT it errors.

    The  sun_total = 150

     

     

  • September 30, 2019 at 10:51 am

    #3684504

    And the error is?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • September 30, 2019 at 11:14 am

    #3684505

    The errors are attached

    Attachments:
    You must be logged in to view attached files.

  • September 30, 2019 at 12:41 pm

    #3684523

    Don't use the Table Designer to change tables. It is utterly buggy and outright dangerous. It may decide to create a new table and copy data over when there is no reason for it. Not only can this take a long time if the table is big, if the operation is interrupted or if there is an error, you may lose data and/or constraints. That is sort of unnecessary when all you want to do is to add a computed column.

    My advice is that you simply use the ALTER TABLE command to add the column

    ALTER TABLE tbl ADD MinuteToHour AS ....

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • September 30, 2019 at 12:44 pm

    #3684526

    Those errors couldn't have come from the code you posted.  You might want to check the code - it has more close parentheses than opens.

    John

  • September 30, 2019 at 1:34 pm

    #3684565

    Thanks for your help, I am still having issues, could you look at the code below and see where I am going wrong

    ALTER TABLE tableName CONVERT(char(5), DATEADD(MINUTE, convert(int,150), '19000101'), 108) as MinuteToHour

    Its probably something simple,

     

    Thanks

  • September 30, 2019 at 1:39 pm

    #3684566

    It is, yes.  You'll find the correct syntax here.

    John

    Edit: even when you get the syntax right, you're always going to get the value "02:30".  It looks as if you want to put the column name in your formula instead of '19000101'.

  • September 30, 2019 at 6:46 pm

    #3684713

    ALTER TABLE dbo.table_name ADD MinuteToHour AS CONVERT(char(5), DATEADD(MINUTE, CONVERT(int,[sun_total]), '19000101'), 108);

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 8 posts - 1 through 7 (of 7 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy