T-SQL (SS2K8) :: Get Difference In Minutes Between 2 Datetime
Apr 26, 2014
I've table and data as follow,
declare @tAccount table
(
isLocked bit,
LastLockoutDate datetime,
currentDte dateTime
)
insert into @tAccount values('true','2014-04-26 16:11:25.337',getdate())
Let's say, current data as follow,
isLocked| LastLockoutDate| currentDte
12014-04-26 16:11:25.3372014-04-27 01:45:15.053
How to get different in minutes between currentDate and LastLockoutDate? Mean, currentDate - LastLockoutDate.
View 3 Replies
ADVERTISEMENT
Mar 27, 2015
I have a datetime field that is just the date and zero for the time element. I also have a varchar field that is of the format 09:25:30
is there an easy way to add these together?
I don't mind if they get converted to integer as it will be used for comparison.
View 9 Replies
View Related
Apr 21, 2015
My table as data as follow,
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
DROP TABLE [dbo].[table_Data]
GO
/****** Object: Table [dbo].[table_Data] Script Date: 04/21/2015 22:07:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_Data]') AND type in (N'U'))
[code].....
View 6 Replies
View Related
Dec 8, 2007
Hi All,
I want to get the date Difference with Day,hours,minutes,seconds of the given 2 dates.(Say the difference of
12/6/2007 7:00:00 AM, 12/8/2007 8:00:00 AM as 2 days 1:00:00)
Is there any inbuilt function in SSRS is available to implement this. Or any other way to do this.Please help me with this .Thanks in advance.
With Thanks
M.Mahendra
View 2 Replies
View Related
Jul 10, 2015
Currently my script is using the below mentioned query to find the time difference.
DATEDIFF(HH,DATEADD(SS,hcreacion,fcreacion) ,DATEADD(SS,hcerrar,fcreacion))
If there is 1 hr 30 minutes time difference, I am getting 2 hours as output. But we need 1.30 as output. is there any way to achieve this?
View 14 Replies
View Related
Sep 16, 2005
I have written a function that returns the number of Days, Hours and minutes from a given number of minutes. On testinf the results are close but not quite there. Can anyone see where I have gone wrong or is there an easier way of doing this? Code is as follows:
CREATE FUNCTION dbo.GetTimeBetweenLong
(@StartTime DateTime, @EndTime DateTime, @CurrentDate DateTime)
RETURNS VarChar(50) AS
BEGIN
DECLARE @TotalTime Numeric
DECLARE @Minutes Numeric
DECLARE @Hours Numeric
DECLARE @Days Numeric
DECLARE @MinutesInDays Numeric
IF @EndTime IS NULL
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @CurrentDate)
SET @Hours = DATEDIFF(Hour, @StartTime, @CurrentDate) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @CurrentDate) - ((@Days * 24)*60) - (@Hours * 60)
END
ELSE
BEGIN
SET @Days = DATEDIFF(Day, @StartTime, @EndTime)
SET @Hours = DATEDIFF(Hour, @StartTime, @EndTime) - (@Days * 24)
SET @Minutes = DATEDIFF(Minute, @StartTime, @EndTime) - ((@Days * 24)*60) - (@Hours * 60)
END
IF(@Days <0)
BEGIN
SET @Days = @Days - @Days - @Days
END
IF (@Hours < 0)
BEGIN
SET @Hours = @Hours - @Hours - @Hours
END
IF (@Minutes <0)
BEGIN
SET @Minutes = @Minutes - @Minutes - @Minutes
END
RETURN CONVERT(nVarChar(10),@Days) + ' Days, ' + CONVERT(nVarChar(5), @Hours) + ' Hours, ' + CONVERT(nVarCHar(5), @Minutes) + ' Mins'
END
View 1 Replies
View Related
Aug 3, 2004
hey all, i need to find the ratio of difference in 2 datetime variables and the difference of another 2 datetime vars. I figured the best way to do it is to convert the difference in both numerator and denominator to number of minutes.
can anyone help ??
View 1 Replies
View Related
Oct 8, 2014
I am having below schema:
CREATE TABLE #Attendance(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StudentID] [int] NOT NULL,
[ClassID] [int] NOT NULL,
[DateAdded] [datetime] default getdate() NOT NULL
) ON [PRIMARY]
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')
[Code] ....
DateAdded column in first table is nothing but in and out time.
Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.
Validations i need to consider are:
If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance
Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.
And by using this thingIi want to calculate total number of minutes student present in the class and number of minutes absent.
If there is only one DateAdded for class then consider as a absent student.
View 7 Replies
View Related
Oct 9, 2007
I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.
View 5 Replies
View Related
Apr 16, 2004
If I subtract 14 days from a datetime field, will the time of day that I run this query affect the resultset? I am running the query during "normal business hours", 8 am - 5 pm, and the records are entered during this time frame as well.
ddave
View 6 Replies
View Related
May 22, 2008
Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:
START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10
RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10
Thanks!
View 8 Replies
View Related
May 29, 2008
[SQL Server 2005]
In a report there are following in parameters: from date, to date, from time and to time.
I need to match these search criteria€™s against start time and stop time in assignments.
The trick: How to write SQL that meets all different cases that can arise.
The query need's to traverse through every assignment,
and see if its start and stop time is in the interval given by the user.
It should then return the minutes from the assignment that is within the interval.
I'm thinking of packaging the minute "extracting" logic in a Scalar-valued function.
It should then take the in parameters from the user, and start time and stop time from the assignment as arguments.
To consider: Crossing midnight.
E.g. the user puts in
From date: 2008-05-24
From time: 22:00:00
To date: 2008-05-26
To time: 02:00:00
Since the user wants to evaluate a time span that crosses midnight,
I'll have to look in to time spans like:
2008-05-24 22:00:00 - 2008-05-24 23:59:59
2008-05-25 00:00:00 - 2008-05-25 02:00:00
2008-05-25 22:00:00 - 2008-05-25 23:59:59
2008-05-26 00:00:00 - 2008-05-26 02:00:00
Furthermore the start- and stop time can be partially in the above interval.
E.g. an assignment starts 2008-05-24 21:46:11 and stops 2008-05-24 22:36:05.
This would then yield from the time spans above 36 minutes.
The combinations seem never-ending€¦
View 9 Replies
View Related
Dec 13, 2007
Dear all,
I need to ignore the hours minutes and seconds elements of a datetime field - I've got a way of doing this in my select statement but it seems to be this can't be the most efficient way!
CONVERT(datetime, CONVERT(nchar(10), db.tab.field, 101)) AS date_key
Is there a better way?
View 4 Replies
View Related
May 21, 2015
I have a table that has a unique ID and a datetime of when something changed.
See example:
IDÂ Â Â TimeStamp
16094Â Â Â 2013-11-25 11:46:38.357
16095Â Â Â 2013-11-25 11:46:38.430
16096Â Â Â 2013-11-25 11:46:38.713
16097Â Â Â 2013-11-25 11:46:38.717
16098Â Â Â 2013-11-25 11:46:38.780
[Code] ....
Is there a way I can calculate the difference between row 16106 and 16105 and enter it in line 10601.
View 10 Replies
View Related
Jun 13, 2008
Hi everybody,
How can I find the difference between two datetime types in SQL 2005 in terms of minutes?
e.g. difference between
cast('2008-06-13 08:29:55.220' as Datetime) and GETDATE()
I tried
Select cast(cast('2008-06-13 08:29:55.220' as Datetime) - GETDATE() as float) and it didn't give the desired result.
any help is greatly appriciated.
devmetz
View 3 Replies
View Related
Nov 20, 2007
Hi ,
Iam using sqlserver reporting services 2005 and in one case I need to find out the difference between two datetime parameters. I need the difference with the time. Can you please help me in this....
Thanks in advance...
View 5 Replies
View Related
Apr 23, 2014
Is there a difference in the following: DECLARE @T1 VARCHAR(50);
SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way
SELECT TOP(1) @t1 = Col1 FROM MyTable; --
define variable second wayI did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
View 8 Replies
View Related
May 27, 2014
I have two similar tables in different database and need to make query to select only the data from the first table that is not available in the second table and there is no unique record for each record , but each row is having different records for example:
CREATE TABLE table1(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,
[Code] ....
Output should be like this
PNR | TKT_NUMBER | DEPACITYNAME | ARRCITYCODE | NAME
fdf44 12669 Sah DXB lamees
View 8 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
Jan 19, 2015
select datediff(wk, '2015-01-11', '2015-01-19') returns 1 (shouldn't it return 2?)
select datediff(wk, '2015-01-10', '2015-01-19') returns 2
View 5 Replies
View Related
Mar 30, 2007
I used this query to get a result
select round(cast(DateDiff(ss, convert(datetime,rf.RECVD_DTTM), convert(datetime,con.ARRIVED_DTTM))/60 as float)/60,2) as LengthOfTime
from customer rf
but i am getting an error ?
"Difference of two datetime columns caused overflow at runtime."
Any idea ?
View 10 Replies
View Related
Sep 23, 2005
At my job is a dts package that is failing in SQL 2005. I am not a SQLexpert. I am just trying to fix. I put the query in Query Analyzerand get this error:(4322 row(s) affected)Server: Msg 535, Level 16, State 1, Line 1Difference of two datetime columns caused overflow at runtime.I am just trying to understand what this means, what I should belooking for and what could be wrong. Here is the query:SELECT i.SerialNumber, '' AS mac_number, DATEDIFF([second], 'Jan 1,1970', s.DateOrdered) AS Support_StartDt, DATEDIFF([second], 'Jan 1,1970',s.Warranty_Enddate) AS Support_EndDt,DATEDIFF([second], 'Jan 1, 1970', c.Registration_Date) ASRegistration_Date, c.FirstName AS enduser_fname,c.LastName AS enduser_lname, c.CompanyName ASenduser_companyname, c.ContactEmail AS enduser_email, c.Address ASenduser_address1,c.Address2 AS enduser_address2, c.City ASenduser_city, c.State AS enduser_state, c.Zip AS enduser_zip,c.WorkPhone AS enduser_phone,c.Fax AS enduser_fax, d.DealerName ASdealer_companyname, d.ContactFirstName AS dealer_fname,d.ContactLastName AS dealer_name,d.Address1 AS dealer_address, d.City ASdealer_city, d.State AS dealer_state, d.Zip AS dealer_zip,d.ContactPhone AS dealer_phone,d.ContactFax AS dealer_fax,ISNULL(SUBSTRING(p.ProductName, 11, LEN(p.ProductName) - 10), 'unknownIWP product') AS product_type, '' AS extra1,'' AS extra2, '' AS extra3, '' AS extra4, '' ASextra5, '' AS extra6, '' AS extra7FROM tblInventory i full outer JOINtblDealers d ON i.DealerID = d.DealerID fullOUTER JOINtblSupport s ON i.InventoryID = s.InventoryIDfull outer JOINtblCustomers c ON s.InventoryID = c.InventoryIDLEFT OUTER JOINtblProducts p ON LEFT(i.SerialNumber,PATINDEX('%-%', i.SerialNumber)) = p.SerialPrefixWHERE i.SerialNumber <> ''Any ideas would be greatly appreciated.
View 2 Replies
View Related
Oct 2, 2014
I can't get a varchar to datetime conversion to work. The varchar data is in the form yyyymmddhhmi
I tried convert(datetime, '200508310926')
also tried cast('200508310926' as datetime) both have error "Conversion failed when converting date and/or time from character string"
Is there a format code that needs to be there? If so, I can't figure out what it should be.
I eventually need to convert these to dates and compare them to getdate, example:
...where convert(datetime, dtfield) >= getdate()-1
View 7 Replies
View Related
Oct 29, 2014
I am trying to produce a report that will show a duration in minutes of a time when a room was occupied for a category. Whilst I have the start and end dates and times, the end user must be able to specify not only a range of dates, but a start and endtime of hours in the day in which they are interested in (it will be applied to all days in the range - they are not allowed to specify a different start/endtime per day).
The example I have is a date range of 6 to 17 October, but they only want the times from 09:00 to 21:00, so if a room was occupied from 08:00 to 11:00 they would only want to know the duration as 120 minutes (09:00 to 11:00) not 180.
The data is supplied by a third party, and duration in minutes is supplied, but it is not much use when they are not interested in the 'real' duration.
CREATE TABLE [dbo].[Evts](
[Location_name] [nvarchar](200) NULL,
[Event_Category] [nvarchar](500) NULL,
[Start_Time] [datetime] NULL,
[End_Time] [datetime] NULL,
[Duration] [int] NULL
[Code] ....
---but the expected output is
Location_nameEvent_Categoryduration
RS8CM240
RS8OD480 -- eliminating 08:00 - 09:00 on 17th
RS8OOT300 -- eliminating 08:00 - 09:00 on 13th
RS8SCC150
RS8SODT1740
RS8 SODT180
RS8SODTB60
RS8SODTNT180
RS8TR60
Looking for info regarding to the minute calculation when the start time specified by the user differs from that of the actual startime?
View 2 Replies
View Related
Nov 14, 2014
I want YYYYMMDD.
If I do this it works:
SELECT CONVERT(varchar(8), GETDATE(), 112)
Gives me this:
20141114
If I treat as parameter it doesn't:
DECLARE @date_start datetime
SET @date_start = CONVERT(varchar(8), GETDATE(), 112)
SELECT @date_start;
Gives me this:
2014-11-14 00:00:00.000
how to pass the converted value in 112 style as a parameter?
View 4 Replies
View Related
May 29, 2014
I am trying to create a stored procedure that Pulls in Chargeable and Non Chargeable hours for our employees however When I run the Stored Procedure I get this error "Conversion failed when converting date and/or time from character string." I am having a hard time figuring out were this is happening in the Stored Procedure. Also I would like to be able to Add a parameter that would be the StartDate and EndDate for which the stored procedure would pull time for.
ALTER PROCEDURE [dbo].[Chargeability]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[Code] ......
View 9 Replies
View Related
Oct 13, 2014
I want to count persons for non overlapping intervalls by room number. Here the data:
SET DATEFORMAT mdy;
DECLARE @PersonTime TABLE
(
ID INT,
Person INT,
Room INT,
Coming DATETIME,
Going DATETIME
[code]....
View 4 Replies
View Related
Oct 13, 2015
I have a store procedure who receive an xml file.
In this store procedure I try to put all record in temporary table for some treatement ..
But I have a problem with one field ...
When I execute the code below I receive this error message :
The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
The problem comes from the field DeleteDate from the second row of the xml file
how I can get the correct conversion for this field ?
declare @tbl table(actorPersonId int, personType int, actorType int, person int
, firstName varchar(100), surname varchar(100), phone varchar(100), email varchar(100)
, publication int, logUsr varchar(20), active bit, delDate datetime )
[Code] .....
View 5 Replies
View Related
Jun 23, 2014
I have an issue with validating an XML file I have from a 3rd party.
Willing to improve and standardise company's process, I have created an XML schema collection (SQL 2008R2), but when I validate the XML file against it I always keep getting the above error: Invalid simple type value dateTime.
Definition of dateTime from w3schools website says:
------ Start of quote ----
"DateTime Data Type
The dateTime data type is used to specify a date and a time.
The dateTime is specified in the following form "YYYY-MM-DDThh:mm:ss" where:
YYYY indicates the year
MM indicates the month
DD indicates the day
T indicates the start of the required time section
hh indicates the hour
mm indicates the minute
ss indicates the second
Note: All components are required!
The following is an example of a dateTime declaration in a schema:
<xs:element name="startdate" type="xs:dateTime"/>
An element in your document might look like this:
<startdate>2002-05-30T09:00:00</startdate>
------ End of quote ----
So I would assume that dates like 2012-10-24T14:23:00 or 2012-10-24T14:15:00 should be fine, but no -fails.
It works though only if I add "Z" at the end. Obviously 3rd party do not want to change anything, as it works for ages.
View 4 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
Apr 27, 2015
I have a table with a datetime field (named "lnDateDone") and an integer field (named "PlannedHour"). I want to build a new datetime field with the same value as lnDateDone and replacing the hour part by the PlannedHour field. Example:
lnDateDone = '2014-04-09 13:22:31.544'
HourPlanned = 14
new datetime field = '2014-04-09 14:00:00'
I tried the following:
CAST(SUBSTRING(CAST(lnDateDone AS VARCHAR(25)),1,12) + CAST(PlannedHour AS VARCHAR(2)) + SUBSTRING(CAST(lnDateDone AS VARCHAR(25)), 15, LEN(lnDateHeureFin)) AS SMALLDATETIME) AS DatePrevue
but it won't work when casted in DateTime format, only in varchar, but I want it in datetime as I need to do compute some values using date manipulation functions.
How can I do that?
View 4 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
Apr 16, 2014
i am trying to take a field that has part of a date in it, so I have to parse it out as follows:
SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
CAST((SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)) as datetime)
I also tried CONVERT(datetime, (SUBSTRING(a1.Field1, 3, 2) + SUBSTRING(a1.Field1,5,2) + '20' + LEFT(a1.Field1,2)), and got the same message.
how I can parse that field, then convert it to a datetime format for running a DATEDIFF statement?
View 9 Replies
View Related
