|
CONVERT(VARCHAR(5), (DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600))
+ '':'' +
CONVERT(VARCHAR(5), (SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60))
+ '':'' +
CONVERT(VARCHAR(5), (DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60))
When I used this.I got output as 0: -13: -16(negative values)
But I will have to get output as 0:13:16
how to remove negative sign from datetime in sql?
|
|
|
|
|
I may have done you a disservice, seems you have changed to datetime, well done.
Not sure if it will work with datetime but you might try ABS()
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
you've still not read the forum guidlines, you've still not learnt to use descriptive subject lines, you've still not learned to keep it in 1 thread.
You'll learn nothing from this response so i'll just paste the correction. Im not going to bother testing it, so check the syntax and try to understand it.
CONVERT(VARCHAR(5), ABS((DATEDIFF(SS,MIN(FIRSTTIMEIN),MAX(LASTTIMEOUT)) / 3600)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) / 3600)))
+ '':'' +
CONVERT(VARCHAR(5), ABS((SUM(DATEDIFF(SS,FIRSTTIMEIN, LASTTIMEOUT)) % 3600 / 60)-(DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 3600 / 60)))
+ '':'' +
CONVERT(VARCHAR(5), ABS((DATEDIFF(SS, MIN(FIRSTTIMEIN), MAX(LASTTIMEOUT)) % 60)-(SUM(DATEDIFF(SS, FIRSTTIMEIN, LASTTIMEOUT)) % 60)))
|
|
|
|
|
Maybe if you swap both datetime arguments in DATEDIFF, your problem is gone?
|
|
|
|
|
It looks like you're trying to get the hour:minute:second difference between two datetime values. The simplest way to do that is subtract them, then convert that value to char and apply the appropriate formatting.
declare @t1 datetime, @t2 datetime
select @t1 = GETDATE()
--Select @t2 = @t1 + 1:02:03 (hh:mm:ss)
select @t2 = DATEADD(hour, 1, dateadd(minute, 2, dateadd(second, 3, @t1)))
select convert(varchar, @t2-@t1, 108)
You can find other formatting options here[^]
|
|
|
|
|
0:-13:-16
Please help me to remove the negative sign.I have to get output as
0:13:16
|
|
|
|
|
Are you asking for something as simple as string functions such as SQL Server's Replace()?
Depending on which database you're using, I'd google "your_database string functions"...
|
|
|
|
|
So are you still storing your date data as strings? Or have you done the sensible thing and changed it to a datetime format.
Sorry I already know the answer otherwise you would not be asking this question.
CHANGE YOUR DATE DATA TO DATATIME FORMAT!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All,
I have a report in which, I have to show the trend values like
sum for the same columns I have to show yesterday, 1week ago and 2weeks ago and 1month ago.
Like for example I have 2 columns and one date column. Now I have to write a query in such a way that
the sum(col1), sum(col2) for 1 day ago, 1 week ago and 1month ago in the same row.
Can anybody please help me in writing this query. Any link or any code snippet will be very much helpfull.
Thanks in advance.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Break it into 4 queries, 1 for each time period doing the appropriate aggregation for each query, include the common key. Then join the 4 queries into 1 using the common key. I would use table vars to hold the temp data.
BTW I would not put my company name in your sig, it can be incorrectly construed (look it up).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
how do i make sql statement to page the rows six per result
|
|
|
|
|
Hi,
There's an example over here[^]
I are Troll 
|
|
|
|
|
Hi All,
I have to write a query in SQL Server 2000/2005/2008 in such a way that, "the sum of 1st Column 2nd row and 2nd Column 1st row is placed in 2nd column 2nd row".
Any kind of link or any suggestion will be very very helpfull.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
In other words, it sounds like column 2 is a running total of column 1. There's not a real elegant way that I know of, but it is a common problem.
Did you try a search for SQL running totals[^]?
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Thanks, I got it in Crystal Reports only. It is fixed and your link also helps me a lot if I need it in SQL Server.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Got myself a head scratcher here. I'm needing to pull employee names and pictures from a database, which I can do with the following select:
SELECT dbo.vwEmployee.BadgeName, dbo.Image.Photo, dbo.vwEmployee.Updated
FROM dbo.Image INNER JOIN
dbo.vwEmployee ON dbo.Image.OwnerGUID = dbo.vwEmployee.OwnerGUID
WHERE (dbo.vwEmployee.Department = 'IT')
Problem is that the application that was used to store the employee information and photos also keeps every photo taken of the employee. I want to be able to return only the row with the most recent photo taken (or the first instance of the most recent update since some have multiple records with the same updated date). I know I can do this with the application I am writing to use this query, but I would rather let the SQL Server do most of the heavy lifting and to further narrow down my results.
|
|
|
|
|
Does the Image table have multiple records or multiple columns for the pictures? If record then does it have a time stamp? If so use a subquery to find the max time and pass that picture.
|
|
|
|
|
If you have a date field, then use TOP 1 and ORDER BY update_date DESC.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
You need to do a sub-select to get a list of the employees and the date of their most recent picture. Then you join to that on the EmployeeID (OwnerGuid) and the date.
select c.BadgeName, a.Photo, c.Updated
from Image a
join (select OwnerGUID, MAX(PictureDate) PictureDate from Image group by OwnerGUID) b on a.OwnerGUID = b.OwnerGUID and a.PictureDate = b.PictureDate
join vwEmployee c ON a.OwnerGUID = c.OwnerGUID
where c.Department = 'IT'
|
|
|
|
|
I have two tables: table1 field contains the ID type UniqueIdentifier and table2 contains ID as foreign key when inserting values is not the same values filled in the two table table1 in this ID is filled eg e210d5ad-BC41-439th-b8e8-e8ed775ee932, and in table 2 it is filled with zero
So how do I do to unify all relationships necessary is active
the two queries are:
CREATE Procedure [dbo].[Insert1](@ID uniqueidentifier, @name varchar(10),@tel varchar(10))
AS
INSERT INTO [table1] ([ID], [name], [tel])
VALUES (NEWID(), @name, @tel)
CREATE Procedure [dbo].[Insert2](@ID uniqueidentifier, @cmd1 varchar(20))
AS
INSERT INTO [Table2] ([ID], [cmd1])
VALUES (@ID, @cmd1)
table1: ID Primary key
Table2:ID foreign Key
Thank you very much for your help
|
|
|
|
|
You would need to either wrap the inserts together and share the id created between them or return the value created by the first query and pass it into the second as a parameetr. I figure that your probably not doing a 1:1 insert here (ie: Theer are goign to be more inserts intot he second table as details?) so I would just return the ID created in the first insert and then pass it into the second as an arg myself.
|
|
|
|
|
Since a UniqueIdentifier is really unique (in contrast to a simple autoincrement integer), you can assign it to the object in your program before inserting it to the database.
|
|
|
|
|
You use NEWID() to generate the new ID key, you need to trap this and pass it back to the client so it can be put into @ID for the second insert.
Insert1
Does not need @ID passed in as it is not used.
Create a new ID @CreatedID in the proc but outside the insert statement
Insert the record using the @CreatedID
After the insert select @CreatedID as NewID will return it to the client
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
Is it possible to create a Ms Excel form where I can enter data which should be automatically enter in an existing Ms Access database?
Thank you in advance
|
|
|
|
|
The answer is yes (might require some vba code). But raises the question, Why not use a form in Access?
PS I'm a bit rusty on Excel/Access programming so don't ask me how. I know it can be done.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
