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