T-SQL (SS2K8) :: Convert Value To Seconds?
Jun 3, 2014
I want to calculate total sum of column.
I have a table with many values of column:
declare @time table
(times nvarchar(255))
INSERT INTO @time (times)
Select ('05:01')
UNION ALL
Select ('00:01')
UNION ALL
Select ('170:01')
UNION ALL
Select ('2543:01')
Select * from @time
How can I get seconds value for every row?
05:01 - 301
00:01 - 1
170:01 - 10201
P.s. Finally I use - Select convert(char,dateadd(second,152581,60),108) as sumtime
View 2 Replies
ADVERTISEMENT
Jul 23, 2005
Hi all.If I've got a query which has a field with seconds in it... how will I usethe Convert function to get my field converted to the format: HH:MM:SS ?The field with the seconds in is called: "Diff"Thanks alotRudi
View 2 Replies
View Related
Nov 2, 2007
Hi,
I have an user input variable called @stop_time which takes input in the format of HH:MM:SS.
I need to convert it into seconds only.
declare @start_time datetime
set @start_time ='02:15:00'
How can I do that?
Can anyone help please?
View 1 Replies
View Related
Sep 21, 2005
Hello every one I have a column in my table which stores seconds.
I want to convert the value into Days:Hours:Minutes:Secons(DD:HH:MM:SS) format.
Can anybody please tell me how to do that.
The value of seconds wil be large (eg. 16798563,..and so on) SO how to I convert this value to the format I need.
URGENT PLZ
Regards
Mohsin Iqbal
View 1 Replies
View Related
Apr 28, 2008
How to Convert Seconds (i.e., 114) to hh:mm:ss format,
is there any way.. i've a column (DURATION) which is recorded in Seconds
AND USER wants to see the seconds in hh:mm:ss 00:14:28
pls.help
thanks
View 2 Replies
View Related
Jun 19, 2008
Hi, I have a field with seconds in it, and I need to convert this so that it shows in the format hh:mm:ss. Is there an easy way to do this?
Thanks in advance,
Ben
View 3 Replies
View Related
Sep 24, 2007
Hello,
How would one convert HH:MM:SS to Seconds?
Thanks in advance!
--Phb
--PhB
View 1 Replies
View Related
Nov 2, 2007
Hi,
I have an user input variable called @stop_time which takes input in the format of HH:MM:SS.
I need to convert it into seconds only.
declare @start_time datetime
set @start_time ='02:15:00'
How can I do that?
Can anyone help please?
View 1 Replies
View Related
Jan 28, 2008
I have values in seconds (integers) in a database column and I want to convert those values to datetime. For example: 102 into 1:42(60 seconds = 1 minute + 42 seconds = 1:42), 150 into 2:30, etc.
I've been working for hours today on this, but haven't been able to come up with a solution.
View 4 Replies
View Related
Oct 28, 2005
I need to convert 300.876 to 5:00.876 in classic asp. Can anyone help?
View 1 Replies
View Related
Nov 26, 2007
Hi
I have a field (int) thats a value in seconds.
I need to add it to a datetime value to get a finish time ..
how can i convert it to datetime (baring in mind its seconds)
so far any conversion i do converts it to days...
View 2 Replies
View Related
Feb 27, 2007
Hello,
I have been trying for a while now to convert seconds which are obtainted from the database into the format mm:ss.
Typically in Excel, I convert my time in integer values divided by 86400 (24/60/60) and then format the cell to "mm:ss" and everything is fine. Now, I am have been trying to reproduce that same logic using Reporting Services (2000) without any success.
I saw on previous post that you can use the format property in RS but if I put in the code "mm:ss" I get the value in text "mm:ss" (i.e.: not the numbers converted).
Any ideas???
View 6 Replies
View Related
May 20, 2014
I'm trying to get the difference between the two times in minutes with the seconds rounded up or down
As an example the difference in the times:
1900-01-01 09:27:49.000, 1900-01-01 09:32:28.000 is 279 seconds which is 4.65 minutes,
using
select
cast(round(datediff(second, convert(datetime, '1900-01-01 09:27:49.000'), convert(datetime, '1900-01-01 09:32:28.000')) / 60, 2) as numeric(18,2))
I am receiving the value 4.00 instead of 5 which I would like 4.65 to round to. How can I get the difference in seconds to round to the minute ?
View 5 Replies
View Related
Oct 7, 2015
converting seconds to HH:MM:SS using SSIS. I know how to do it in T-SQL.
View 13 Replies
View Related
Feb 15, 2008
I have a column of data that is the number of seconds. I need to format this column into the HH:MMS format. If there are 130 seconds the second column should read 00:02:10. How can I accomplish this in report builder or is it even possible in Report builder? I can use the following formula in SQL but is there a way to do it in Report Builder?
CONVERT(varchar(6), talktime / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2),
talktime % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar(2), talktime % 60), 2)
Thanks
View 3 Replies
View Related
Sep 21, 2007
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
View 8 Replies
View Related
Sep 5, 2014
I'm importing dates into a table with Bulk insert
SET DATEFORMAT DMY
it works with dates e.g. "14/01/2009"
However sometimes I get dates in the format
"Fri 14/01/2009"
What is the best way to convert these.
I can only think of putting them in a staging table with all date fields as varchars Then updating these varchar fields
LTRIM(REPLACE(dtField, 'Mon', ''))
LTRIM(REPLACE(dtField, 'Tues', ''))
View 9 Replies
View Related
Sep 17, 2014
I work with SQLite and need to write a query the old school way to convert rows to columns. If it was MS SQL I would use pivot to get the expected result. However this is SQLite I cannot use pivot.
Sample data:
create table t1 (id int, Dept char (1), Total int);
insert t1
select 1, 'A', 100
union
select 2, 'B', 120
union
select 3, 'C', 140
union
select 4, 'D', 150;
How do I use LEFT OUTER JOIN to produce result similar to the below?
SELECT 'Total' AS Dept,
[A], [B], [C], [D]
from
[code]....
View 7 Replies
View Related
Sep 22, 2014
I have to store the result of a calculation in a column of type CHAR(7) (and am unable to change the column type).
The calculation can have results ranging in size from 0.1234567 to 99999999.
In the first case, I would need to store the value of 0.12345 in the column. In the later case, an error should be thrown.
So I need to store all of the significant digits from the left of the decimal (if there are < 7) and as many of the digits to the right as will fit into a CHAR(7), with the remaining precision being truncated.
View 6 Replies
View Related
Feb 11, 2015
I have the following CASE statement:
SELECT CASE WHEN [TBL_whole_rock_geochem].au = 0 THEN '<' + [TBL_ActLab_codes].[Au] ELSE [TBL_whole_rock_geochem].[Au] END AS [Au det]
It errors on the highlighted code.
Error converting data type nvarchar to float
the code should return :
<0.5
<1
I have tried '<' + ISNUMERIC([TBL_ActLab_codes].[Au]) but received this error
Conversion failed when converting the varchar value '<' to data type int.
How I can concatenate the < with a float value?
View 5 Replies
View Related
Jun 5, 2015
I am new to SSC . I have a table that has NO.OF WORKING HOURS for 30 days. I am trying to convert all the values into INT as MINS to generate a SSRS report.
Note: There are some records with negative value (-4h15m)
Sample
NAME D1 D2 D3 ....D31
X1 9H 30M NULL 10M
X2 0M 1H 30M -4H45M
X3 20M -1H NULL
View 6 Replies
View Related
Mar 12, 2014
How to get this out put.
Details:
declare @deadline Datetime = '2014-03-23 15:30:10.000'
SELECT CONVERT(VARCHAR(30),@deadline, 100) AS DateConvert
----With this I am able to produce that like
----o/p: Mar 23 2014 3:30PM
declare @deadline1 Datetime = '2014-03-03 15:30:10.000'
SELECT CONVERT(VARCHAR(24),@deadline1, 100) AS DateConvert
--o/p: Mar 3 2014 3:30PM
--expected O/p: Mar 03 2014 03:30PM
What is the correct date format to achieve this.
View 2 Replies
View Related
Apr 2, 2014
I seem to always get the "Fun Stuff" to try and figure out. I have an entire table that was pumped out of Oracle. I even hate saying that word!
There are a couple columns that are Float data type, and they are storing phone numbers as a Float data type. I am not able to CAST these into anything that is legible.
This is one of the values that I made up that look like some of the others.
9.72732e+009
View 2 Replies
View Related
Nov 13, 2014
I have 5 columns in my database. 1 column is coming like a dynamic.
I want to convert records from rows to columns. Currently I have a data like this.
Race AgeRange Amount
W 17-20 500
W 21-30 400
W 31-40 200
A 17-20 100
H 41-50 250
H 51-60 290
So age range is not fixed and it can be any and I have one separate relational table for age range where it's coming from. Now I want to convert it into columns like
Race 17-20 21-30 31-40 41-50 51-60
W 500 400 200 0 0
A 100 0 0 0 0
H 0 0 0 250 290
View 3 Replies
View Related
Nov 14, 2014
I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.
Current record
RecordID Name CountyList
1 ABC 00
2 CDE 01020304
3 XYZ NULL
Expected output:
RecordID Name CountyList
1 ABC 00
2 CDE 01
2 CDE 02
2 CDE 03
2 CDE 04
3 XYZ NULL
I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).
View 2 Replies
View Related
Mar 16, 2015
Why does this code (below) cause a change in the transaction state?
DECLARE @date DATE,
@string VARCHAR(5)= 'test'
BEGIN TRAN
PRINT XACT_STATE()
[Code] ....
View 8 Replies
View Related
Mar 21, 2014
Code snippet for changing '00001001001' into a table value construct containing (64,8,1)?
View 6 Replies
View Related
Apr 14, 2014
I have cpt codes in qry1 that need to be converted then match against qry2 and return the unmatched.
Example:
Cpt 88305 in qry1 can be either g4164 or g4165 in qry2
Cpt 88174 in qry1 can be either g3564 or g3565 or g3566 in qry2
select
Invoice_number
,cpt
From qry1
Select
Invoice_number
,cpt
From qry2
What is the easiest way to get the unmatched from qry1 against qry2 joining on invoice_number and converted cpt?
View 1 Replies
View Related
Apr 25, 2014
I have this query
SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code
and the output
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10
How can I modify the query to come up with output below,
Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10
View 9 Replies
View Related
Jul 2, 2014
Need getting the below Cursor query convert to a Recursive CTE or with a while loop as I do not want to use a cursor.
Declare @Companyname Nvarchar (400)
declare @str nvarchar(MAX)
TRUNCATE TABLE STAGING.dbo.[IT_G_L Entry]
DECLARE GLEntry_cursor CURSOR FOR
SELECT REPLACE Name FROM Company where Name <> 'AAAAA'
OPEN GlEntry_cursor
[Code] ....
View 9 Replies
View Related
Sep 4, 2014
Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.
Below is sample of data:
create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')
Required output
depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2
View 2 Replies
View Related
Dec 21, 2014
I have a standard datetime and I need to convert it to the client specification of:
YYYY-MM-DDThh:mm:ssTZD
eg: 2009-04-16T19:20:30+08:00
I am not sure of the easiest way to do this.
The test code below gets me part of the way but I am unsure on how to get the offset on the end without hardcoding to much.
DECLARE @datetime DATETIME = '2014-12-20 12:30:00'
SELECT CONVERT(VARCHAR(30),@datetime,127)
View 2 Replies
View Related
Aug 3, 2015
We have 2 columns:
StartDate - data type Date
StartTime - data type Time
I need to combine them into a DateTime data type. For now, I convert each of them into varchar, insert space in between, and convert to DateTime, like this:
convert(datetime, convert(varchar(10), EndDate , 101) + ' ' + CONVERT(varchar(20), EndTime,24))
Is there a better, more elegant way to do this?
View 9 Replies
View Related