How Do I Subtract Time?
Dec 10, 2007
I have a View in SQL,
SELECT dbo.ATTTblAttendance.LogIn, dbo.ATTTblAttendance.LogOut,
ISNULL(DATEDIFF(Hour, dbo.ATTTblAttendance.LogIn, dbo.ATTTblAttendance.LogOut),0) AS TimeWorked
FROM dbo.ATTTblAttendance
Currently, when Login = 1:00 PM and LogOut = 4:30 PM, TimeWorked = 3.
What I need is for TimeWorked to = 3.5
My ASP.NET page has the following:
<asp:TemplateField HeaderText ="Total Hours" >
<ItemTemplate >
<asp:Label ID="TimeWorked" runat="server" Text='<%# Eval("TimeWorked") %>' ></asp:Label>
</ItemTemplate>
</asp:TemplateField>
The goal is to take a person's dailt time in and time out, and subtract their AM and PM break time to yield their TimeWorked.
Ex. time in > 8:00 AM,
lunch > :30 minutes
time out > 4:30 PM
Total Time Worked today = 8.0 or 7.45, or 7.5, if the time warrant it.
How I get this to subtract correctly?
Ayomide
View 3 Replies
ADVERTISEMENT
Feb 5, 2007
Hi guys
I have a column with datas as
"0.0"
"123.0"
"45.0"
i have a query were i have to match the value only. i don't want the decimal and followed by the any thing.
or
how do i extract just the value not the decimal part so that i can compare it with other value.
how do i do that.
thanks
View 10 Replies
View Related
Jun 24, 2004
Hello everyone. I have a query where I pull all item_codes that start with a 7. I need all the item_codes that start with 7, but I need to subtract the last 3 characters from only the item_codes that are not 71860kit or 71851nggun. How would I be able to accomplish this. What I have below subtracts the last 3 characters from all item_codes including 71860kit and 71851nggun. I want these two to stay in tact when I select them in the query.
This is the Query that I have now!
select datepart(year, date_cust_invoice)as INV_YEAR,
datepart(month, date_cust_invoice)as INV_MONTH,
item_code=left(item_code, len(item_code)-3)
, sum(QTY_SALES)as QTY_SALES_TOTALS, sum(SALES_VAL)as SALES_VAL_TOTALS
from opcsahf
where datepart(year, date_cust_invoice) >= '2003'
and substring(item_code, 1, 1) = '7'
group by datepart(year, date_cust_invoice), datepart(month, date_cust_invoice),
item_code
order by inv_year, inv_month, item_code
All help is appreciated!
View 1 Replies
View Related
Nov 5, 2007
Hi,I am trying to solve the following issue, can anybody throw some lights...Here is some sample data from the tableTIME STAMP PROJECTID FUNCTION2007-10-31-01.10.05.00 3333311111 First2007-10-31-01.10.06.00 3333311111 Second2007-10-31-01.10.08.00 3333311111 Third2007-10-31-01.10.10.00 3333311111 Complete2007-10-31-01.10.11.00 2222244444 First2007-10-31-01.10.12.00 2222244444 Second2007-10-31-01.10.14.00 2222244444 Third2007-10-31-01.10.15.00 2222244444 Complete... and result I am looking something like below PROJECT ID FUNCTION DURATION3333311111 First 0:00:01 (2007-10-31-01.10.06.00 - 2007-10-31-01.10.05.00) 3333311111 Second 0:00:06 (2007-10-31-01.10.08.00 - 2007-10-31-01.10.06.00)3333311111 Third 0:00:02 (2007-10-31-01.10.10.00 - 2007-10-31-01.10.08.00)2222244444 First 0:00:01 (2007-10-31-01.10.12.00 - 2007-10-31-01.10.11.00)2222244444 Second 0:00:02 (2007-10-31-01.10.14.00 - 2007-10-31-01.10.12.00)2222244444 Third 0:00:01 (2007-10-31-01.10.15.00 - 2007-10-31-01.10.14.00)hope the question is clear!thanks for your time, Murali
View 11 Replies
View Related
Oct 18, 2007
I want to fetch all the records which are 2 minutes older
How can I do it?
View 2 Replies
View Related
Sep 8, 2013
select Doctor, count(monthyear)- count(paidinfull) as Patients
from tableA
where paidinfull = 'YES'
group by doctor
So I have a table like
Doctors Patients
------ --------
Burns 12
White 7
where Doctors is a group by from tableA and Patients is a result of the row count of paidinfull = yes minus the rowcount of monthyear.
View 5 Replies
View Related
Sep 30, 2013
I have data for moths which are commulative, for example Feb where month is 02, ecul Jan. + Feb. data. I would like to have the data for only Feb = when month '02' Feb = data for '02' - data for '01'. I would like to writ these commands: For Jan there is no problem to write the command but for other months I am not sure how to write the commands for months other than Jan.
,Jan_qty_1 = case
when [stat_month] = '01' then cast(sum(cast([qty_1_mo] as bigint)) as bigint)
else CAST('0' as int)
end
[Code] ....
here is the data:
ust_code district stat_year stat_monthqty_1_mo
24105220070112619
2410522007022200
2410522007024438
2410522007030
24105220070314434
2410522007042295
2410522007040
2410522007050
2410522007050
241052200706454
View 3 Replies
View Related
Dec 2, 2014
How to subtract days from the given format...
format : Mon, 03 Nov 2014 14:10:00 GMT
and it is in varchar(50) under dates column the data is inserted
View 1 Replies
View Related
Oct 1, 2006
Hi all,
I need help writing a query that will subtract the values of 2 rows from the same column to display in the result set. Some background information: a table has a sales column that keeps track of sales by the minute, but this is done in a cumulative manner, i.e, sales at row 3(minute 3) = sales recorded @ minute 2 plus sales @ minute 3. Therefor to get the actual sale at min 3, i would have subtract value at row 2 from row 3. make sense? it sounds very easy but I am having a hard time refering back to the previous row and am dealing with more than 1000 rows. i thought about doing a self join on the table but could not get it to do what i want.
would appreciate any help i can get. thanks
View 5 Replies
View Related
Oct 13, 2006
Hi,
I have an implementation of the UDT - 3-dimentional vector. In my code I have implemented add, subtract and multiply methods for the type. I have also implemented overloaded operators for +/-/* in my C# code. Those overloaded operator are working as expected in C# tests. However when I€™m trying to use +/-/* operators in T-SQL over my UDT it returns the following error:
Invalid operator for data type. Operator equals add, type equals Vector.
The following fragment does work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1.[Add](@v2) 'v1 + v2'
And this fragment does not work:
DECLARE @v1 Vector, @v2 Vector, @v3 Vector;
SELECT @v1 = CAST('1,1,1' as Vector), @v2 = CAST('2,2,2' as Vector)
SELECT @v1 'v1', @v2 'v2', @v1+@v2 'v1 + v2'
I guess that SQL Server is not aware of the operators€™ overload I have implemented in the C# code. Is there any way to instruct SQL Server to use overloaded operators in the T-SQL so the code will look naturally @a + @b instead of @a.[Add](@b) and as a result use standard summary functions SUM() instead of writing user defined aggregate function for the Vector type field?
Maxim
View 4 Replies
View Related
Mar 29, 2008
Hi Guys,
Does anyone have a good example how to subtract an amount from an sql table?
At present i have a table such as this
id
View 2 Replies
View Related
Sep 15, 1999
Can any one tell me how to subtracts and add hours to the current date and time?
In my case I have to store the Vancouver date and time in Toronto.
Thanks,
T.Lingam
View 1 Replies
View Related
Jan 12, 2005
Hey all, how do you take the current date and subtract a year from it in a SP?
What I want to do is...
Take the current date when the SP is ran, subtract a year, then if my date field is within that range (higher than the date with the subtracted year) it will continue in the query.
Edit: the answer is..
dateadd(yyyy, -1, getdate()) as Date1
View 6 Replies
View Related
Nov 20, 2007
I have a query that uses rollup to get totals and top 101 to restrict the rows to top 100 rows plus the total column. The total column shows total for all rows not just the top 100 which is exactly what i need. The only thing I need now is a row that shows total for all rows that have been excluded, or the total row - total of the top 100.
Here's the query: (Biggest factor i have to keep in mind is query speed.)
Code Block
WITH CTE_name AS (
SELECT TOP 101 b.name,
SUM(ISNULL(CAST(b.launched AS BIGINT),0)) AS quantityPrepared,
SUM(ISNULL(CAST(b.bounced AS BIGINT),0)) AS bounced,
SUM(ISNULL(CAST(b.delivered AS BIGINT),0)) AS delivered
FROM dimension tt
INNER HASH JOIN batch b ON (b.batch_id = tt.batch_id)
WHERE tt.datelaunched >= '11/19/2007'
AND tt.datelaunched < '11/20/2007'
AND tt.id = 1
GROUP BY b.name WITH ROLLUP
ORDER BY SUM(ISNULL(CAST(b.launched AS BIGINT),0)) DESC
)
SELECT
name,
quantityPrepared
bounced,
delivered
FROM CTE_name
ORDER BY 2
View 1 Replies
View Related
Mar 4, 2008
i get an error whan i do this
Code Snippet
SELECT DATEADD(day, -7, StartDate)AS [sevenDayAgo]
FROM dbo.empList
Msg 517, Level 16, State 2, Line 1
Adding a value to a 'smalldatetime' column caused overflow.
my field StartDate is smalldatetime
TNX
View 5 Replies
View Related
Mar 12, 2007
Hi,
I've searched quite a bit for help with this syntax but have given up.
I need help with the where clause of a query using SQL SERVER that selects records a certain number of days before the current date. I have tried this and it's incorrect syntax:
WHERE (fldDate < ({ fn NOW() - 500 })
Can someone please help me out with correct syntax for this? thanks much.
View 3 Replies
View Related
Dec 5, 2015
How can i subtract two columns:
I have table:
   Â
ID COL 1 COL 2
-------------------------
1 200.00 70.00
2 200.00 30.00
3 200.00 90.00
4 200.00 110.00Col1 - COL2
But to continue for each row and value is reduced by the previously value.
My output should be like as:
ID COL 1 COL 2 [COL3 AS RESULT]
-------------------------
1 200.00 70.00 130
2 200.00 30.00 100
3 200.00 90.00 10
4 200.00 110.00 -100
View 3 Replies
View Related
Feb 4, 2015
We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.
select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())
In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())
For both we get the following error "Arithmetic overflow error converting expression to data type datetime."
View 7 Replies
View Related
Dec 11, 2007
Im making a shopping cart website for a school project in ASP.net with VB. I need help subtracting the quantity purchased (its saved in a session) from the stock number saved in a database.I know this:UPDATE inventory SET stock = stock - <quantity_purchased> WHERE id = <inventory_id>But I dont understand how to get the quantity purchased from the session to <quantity_purchased>. I tried putting the name of the session there and I got an error, i tried saving the session into a dim didnt work either.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [stock] FROM [product]" InsertCommand="INSERT INTO [product] ([stock]) VALUES (@stock)" UpdateCommand="UPDATE product SET stock = (stock - @Quantity) WHERE (productID = @productID)">
<InsertParameters>
<asp:Parameter Name="stock" Type="Int16" />
</InsertParameters>
<UpdateParameters>
<asp:SessionParameter Name="Quantity" SessionField="Quantity" Type="Int32" />
<asp:SessionParameter Name="productID" SessionField="productID" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
and I have than in my VB code on submit : SqlDataSource1.Update()
View 1 Replies
View Related
Mar 20, 2015
I have a query which provides the below result set:
1165 6
1,173.0013
9740 6
9820 13
2271 6
2287 13
10,952.006
11,029.0013
4,074.006
4,103.0013
I want to achieve something like below. It should subtract the '13' row to '6' row and provide another column with the result. the '6' and '13' category code share the same Key.
1165 6 -8.00
1,173.0013-8.00
9740 6 -80
9820 13 -80
[code]...
View 5 Replies
View Related
Jun 22, 2015
I have a list of patient encounter dates ordered by the date. I need to subtract the previous date in order to get the number of days between each date for the same patient.
create table TEST
(
MRN varchar(10),
EncDTTM datetime,
Sequence int
)
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-01-24','1')
insert into TEST(MRN, EncDTTM, Sequence) values( '00000203','2014-02-03','2')
[code]....
View 9 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
May 26, 2014
I got a sales cost and cost amount table for my budget. the sales cost table is getting updated with FOBB items which makes the total incorrect . the FOBB values needs to be moved from the sales cost column to the cost amount column. how can i do it with an SQL script.
View 1 Replies
View Related
Oct 14, 2015
I have the following table
Table Name EmployeeInformation
EmployeeID EmployeeFirstName EmployeeLastName
  1       |John            |Baker
  2       |Carl             |Lennon
  3       |Marion           |Herbert
Table Name PeriodInformation
PeriodID PeriodStart PeriodEnd
  1     |1/1/14    |12/30/14
  2     |1/1/15    |12/30/15
[code]...
I want a query to join all this tables based on EmployeeID, PeriodID and LeaveTypeIDÂ sum of LeaveEntitlement.LeaveEntitlementDaysNumber based on LeaveTypeID AS EntitleAnnaul and AS EntitleSick and sum AssignedLeave.AssignedLeaveDaysNumber based on LeaveTypeIDÂ Â AS AssignedAnnaul and AS AssignedSick and subtract EntitleAnnaul from AssignedAnnual based on LeaveTypeIDÂ Â AS AnnualBalance and subtract EntitleSick from AssignedSick based on LeaveTypeIDÂ Â AS SickBalance
and the table should be shown as below after executing the query
EmployeeID, EmployeeFirstName, EmployeeLastName, PeriodID, PeriodStart, PeriodEnd, EntitleAnnual, AssignedAnnual, AnnualBalance, EntitleSick, AssignedSick, SickBalance
View 4 Replies
View Related
Sep 7, 2006
I have a table like this:
Bottom Top
0 2
4 6.5
9 14
15 17
In Transact SQL I need to calculate the difference between the current bottom and the previous top. If there is no previous top, then the result would be the current bottom.
So, the result would be 0, 2, 2.5, 1
I must return the value in a table. Thank you all for your help!
Karen
View 9 Replies
View Related
Apr 25, 2014
Sample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
View 7 Replies
View Related
Jun 11, 2015
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
View 5 Replies
View Related
Oct 22, 2015
I am trying to load previous days data at 3 am via a SSIS job.
The Date variable is initiated as DATEADD("dd",-1, GETDATE()) in the for loop.
Now, as this job runs at 3 am, and I set the variable as GETDATE() - 1, it excluded the data from 12 am to 3 am in the resultset as Date is set as YYYY-MM-DD 03:00:00:000 I need this to be set as YYYY-MM-DD 00:00:00:000
How can i do this?Â
View 2 Replies
View Related
Oct 3, 2015
I hope to update a DateTime column value with a Time input parameter.  Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
View 14 Replies
View Related
Dec 15, 2006
I am using VS2005 (VB) to develop a PPC WM5.0 Program. And I am using SQLCE 3.0. My PPC Hardware is in 400MHz.
The question is when the program try to insert the first record into sdf database after each time the program started. It takes a long time. Does anyone know why and how can I fix it?
I will load the whole database into a dataset when the program start and do all the "Insert", "Update", "Delete" in this dataset and fill it into database after each action.
cn.Open()
sda = New SqlCeDataAdapter(SQL, cn) 'SQL = Select * From Table
scb = New SqlCeCommandBuilder(sda)
sda.Update(dataset)
cn.Close()
I check the sda.update(), it takes about 0.08s for filling one record into database normally. But:
1. Start the PPC Program
2. Load DB into dataset
3. Create a ONE new record in dataset
4. Fill back to DB
When I take this four steps everytime, the filling time is almost 1s or even more!
Actually, 0.08s is just a normal case. Sometimes, it still takes over 1s to filling back a dataset which only inserted one record when the program is running. (Even all inserted records are exactly the same in data jsut different in the integer key)
However, when I give up the dataset and using the following code:
cn.Open()
Dim cmd As New SqlCeCommand(SQL, cn) ' I have build the insert SQL before (Insert Into Table values(XXXXXXXXXXXXXXX All field)
cmd.CommandType = CommandType.Text
cmd.ExecuteNonQuery()
cn.Close()
StartTime = Environment.TickCount
I found that it is still the same that the first inserted record takes more time, but just about 0.2s. And the normal insert time is around 0.02s. It is 4 times faster!!!
View 1 Replies
View Related
Apr 21, 2015
SELECTÂ
  CONVERT(VARCHAR(10),attnc_chkin_dt,101) as INDATE,
  CONVERT(VARCHAR(10),attnc_chkin_dt,108) as TimePart
FROM pmt_attendance
o/p
indate   04/18/2015
time part :17:45:00
I need to convert this 17:45:00 to 12 hours date format...
View 8 Replies
View Related
Nov 12, 2007
Hi,
We need to select rows from the database that have been recently inserted/updated. We have a main primary table (COMMIT_TEST) and a second update table (COMMIT_TEST_UPDATE). The update table contains the primary key and a LAST_UPDATE field which is a datetime (to tell us when an update occurred). Triggers on the primary table are used to populate the update table.
If we insert or update the primary table in a transaction, we would expect that the datetime of the insert/update would be at the commit, however it seems that the insert/update statement is cached and getdate() is executed at the time of the cache instead of the commit. This causes problems as we select rows based on LAST_UPDATE and a commit may occur later but the earlier insert timestamp is saved to the database and we miss that update.
We would like to know if there is anyway to tell the SQL Server to not execute the function getdate() until the commit, or any other way to get the commit to create the correct timestamp.
We are using default isolation level. We have tried using getdate(), current_timestamp and even {fn Now()} with the same results. SQL Queries that reproduce the problem are provided below:
/* Different functions to get current timestamp €“ all have been tested to produce the same results */
/*
SELECT GETDATE()
GO
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn Now()}
GO
*/
/* Use these statements to delete the tables to allow recreate of the tables */
/*
DROP TABLE COMMIT_TEST
DROP TABLE COMMIT_TEST_UPDATE
*/
/* Create a primary table and an UPDATE table to store the date/time when the primary table is modified */
CREATE TABLE dbo.COMMIT_TEST (PKEY int PRIMARY KEY, timestamp) /* ROW_VERSION rowversion */
GO
CREATE TABLE dbo.COMMIT_TEST_UPDATE (PKEY int PRIMARY KEY, LAST_UPDATE datetime, timestamp ) /* ROW_VERSION rowversion */
GO
/* Use these statements to delete the triggers to allow reinsert */
/*
drop trigger LOG_COMMIT_TEST_INSERT
drop trigger LOG_COMMIT_TEST_UPDATE
drop trigger LOG_COMMIT_TEST_DELETE
*/
/* Create insert, update and delete triggers */
create trigger LOG_COMMIT_TEST_INSERT on COMMIT_TEST for INSERT as
begin
declare @time datetime
select @time = getdate()
insert into COMMIT_TEST_UPDATE (PKEY,LAST_UPDATE)
select PKEY, getdate()
from inserted
end
GO
create trigger LOG_COMMIT_TEST_UPDATE on COMMIT_TEST for UPDATE as
begin
declare @time datetime
select @time = getdate()
update COMMIT_TEST_UPDATE
set LAST_UPDATE = getdate()
from COMMIT_TEST_UPDATE, deleted, inserted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
GO
/* In our application deletes should never occur so we don€™t log when they get modified we just delete them from the UPDATE table */
create trigger LOG_COMMIT_TEST_DELETE on COMMIT_TEST for DELETE as
begin
if ( select count(*) from deleted ) > 0
begin
delete COMMIT_TEST_UPDATE
from COMMIT_TEST_UPDATE, deleted
where COMMIT_TEST_UPDATE.PKEY = deleted.PKEY
end
end
GO
/* Delete any previous inserted record to avoid errors when inserting */
DELETE COMMIT_TEST WHERE PKEY = 1
GO
/* What is the current date/time */
SELECT GETDATE()
GO
BEGIN TRANSACTION
GO
/* Insert a record into the primary table */
INSERT COMMIT_TEST (PKEY) VALUES (1)
GO
/* Simulate additional processing within this transaction */
WAITFOR DELAY '00:00:10'
GO
/* We expect at this point that the date is written to the database (or at least we need some way for this to happen) */
COMMIT TRANSACTION
GO
/* get the current date to show us what date/time should have been committed to the database */
SELECT GETDATE()
GO
/* Select results from the table €“ we see that the timestamp is 10 seconds older than the commit, in other words it was evaluated at */
/* the insert statement, even though the row could not be read with a SELECT as it was uncommitted */
SELECT * FROM COMMIT_TEST
GO
SELECT * FROM COMMIT_TEST_UPDATE
Any help would be appreciated, we understand we could make changes to the application/database to approximate what we need, but all the solutions have identified suffer from possible performance issues, or could still lead to missing deals (assuming the commit time is larger than some artifical time window).
Regards,
Mark
View 8 Replies
View Related
Sep 21, 2015
I need to do a time test for restoring an Azure SQL database from a point in time. Can I automate this through PowerShell.
View 3 Replies
View Related
