Need help

  • Hi,

    I have below TableA in database under SQL SERVER 2008 R2

    CREATE TABLE [dbo].[TableA](

    [Server] [nvarchar](256) NULL,

    [DatabaseName] [nvarchar](256) NULL,

    [BackupStartDate] [varchar](20) NULL,

    [BackupFinishDate] [varchar](20) NULL,

    [File_Size] [float] NULL,

    [Time] [numeric](10, 2) NULL,

    [Physical_Name] [varchar](500) NULL,

    [BackupsetName] [nvarchar](356) NULL

    )

    How can we convert the Time which is present in minutes under TableA to the format Hours:Minutes

    I was using the below conversion but it was giving values more than 60 after decimal point. I need to have values to be displayed under TobeConverted Column or Time Column in the Hour:Minutes Format

    SELECT

    [Time],

    [Time]/60 +([Time]%60 ) as TobeConverted

    FROM [dbo].[TableA]

    GO

    Time_TookTobeConverted

    0.00 0.000000

    0.02 0.020333

    0.02 0.020333

    0.07 0.071166

    0.02 0.020333

    10.55 10.725833

    0.12 0.122000

    37.10 37.718333

    1.00 1.016666

    Please let me know.

    Thank You,

  • sql2k8 (7/18/2011)


    Hi,

    I have below TableA in database under SQL SERVER 2008 R2

    CREATE TABLE [dbo].[TableA](

    [Server] [nvarchar](256) NULL,

    [DatabaseName] [nvarchar](256) NULL,

    [BackupStartDate] [varchar](20) NULL,

    [BackupFinishDate] [varchar](20) NULL,

    [File_Size] [float] NULL,

    [Time] [numeric](10, 2) NULL,

    [Physical_Name] [varchar](500) NULL,

    [BackupsetName] [nvarchar](356) NULL

    )

    How can we convert the Time which is present in minutes under TableA to the format Hours:Minutes

    I was using the below conversion but it was giving values more than 60 after decimal point. I need to have values to be displayed under TobeConverted Column or Time Column in the Hour:Minutes Format

    SELECT

    [Time],

    [Time]/60 +([Time]%60 ) as TobeConverted

    FROM [dbo].[TableA]

    GO

    Time_TookTobeConverted

    0.00 0.000000

    0.02 0.020333

    0.02 0.020333

    0.07 0.071166

    0.02 0.020333

    10.55 10.725833

    0.12 0.122000

    37.10 37.718333

    1.00 1.016666

    Please let me know.

    Thank You,

    Look at your output, you are wanting character data and you have created a math addition of two values. The whole thing you have going on just isn't really going to get you what you are trying. I think something like the following is pretty close to what you want.

    declare @Time numeric(9,2)

    set @Time = 59.87

    --set @Time = 97.10

    select @Time/60 +(@Time%60 ) , @Time / 60, @Time % 60

    select cast(@Time as int) / 60 as hours, @Time % 60 as minutes, cast(cast(@Time as int) / 60 as varchar(10)) + ':' + cast(cast(@Time % 60 as int) as varchar(2))

    This is pretty ugly being forced to convert to int and varchar but it will get you what you want. Not really sure why you are storing this is a numeric when you say it is minutes and you in effect ignoring everything after the decimal point.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • This code worked for me.

    SELECT

    [Time],

    cast(cast([Time]/60 as int) as varchar) + ':' + CAST(CAST([Time]%60 as int) as varchar) as 'hr:mm'

    FROM [dbo].[TableA]

    GO

    Thank You very much for your help

  • sql2k8 (7/19/2011)


    This code worked for me.

    SELECT

    [Time],

    cast(cast([Time]/60 as int) as varchar) + ':' + CAST(CAST([Time]%60 as int) as varchar) as 'hr:mm'

    FROM [dbo].[TableA]

    GO

    Thank You very much for your help

    Good to hear it works for you.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Forgetting about the table issues, here is a way to convert minutes to hh:mm format:

    declare @time numeric(10,2)

    set @time = 97.10

    select convert(char(5),dateadd(minute,@time,0),8)

    Of course, dateadd takes an integer for the increment, so your numeric will be implicitly converted to an integer and you may want to control the rounding yourself before passing the value to dateadd....

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Hi

    Just looking at your code there, I know the time in sysjobhistory is poked. It requires a lot of calcs to be done.

    If you are in fact working out some backup times.

    Looks in msdb for

    backupset

    backupmedia

    backupmediafamily

    Those tables have all the data you need to backup reports and have been around since 2k.

    They also have history post sysjobhistory.

    FYI

    Cheers

    Jannie

  • Viewing 6 posts - 1 through 5 (of 5 total)

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