July 18, 2011 at 9:03 pm
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,
July 18, 2011 at 10:03 pm
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/
July 19, 2011 at 8:48 am
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
July 19, 2011 at 5:02 pm
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
July 19, 2011 at 5:03 pm
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);July 19, 2011 at 7:20 pm
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