Calculate Differences Between Two Dates
Sep 9, 2014How can we calculate the difference between two dates (years, months and days)
example:
between '01 / 01/2011 'and '05 / 04/2014' I would have years, months and days
ADVERTISEMENT
Calculate Differences Between Records?
Feb 9, 2015I would like to calculate the date difference between two records:
IDStartEnd
12/9/15 13:28:412/9/15 13:28:46
22/9/15 13:29:032/9/15 13:29:12
32/9/15 13:29:172/9/15 13:29:21
42/9/15 13:29:272/9/15 13:29:31
So I need to find the difference between two consecutive records and put the result in a new column called Downtime. For example:
Record 2 Start (2/9/15 13:29:03) - Record 1 End (2/9/15 13:28:46) = 0:00:17
Record 3 Start (2/9/15 13:29:17) - Record 2 End (2/9/15 13:29:12) = 0:00:05
Record 4 Start (2/9/15 13:29:27) - Record 3 End (2/9/15 13:29:21) = 0:00:06
and so on…
Also what do I do about the 1st record since there is no previous record to subtract from?
So far I have this code in my query to generate my table: SELECT Start, End FROM group_table3
My desired output should look like this:
IDStartEndDowntime
12/9/15 13:28:412/9/15 13:28:46#VALUE!
22/9/15 13:29:032/9/15 13:29:120:00:17
32/9/15 13:29:172/9/15 13:29:210:00:05
42/9/15 13:29:272/9/15 13:29:310:00:06
T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates
Jul 18, 2014Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,
[code]....
Finding Differences Between Dates In A Single Colu
Feb 28, 2008I have a log table that displays the history of the status of an entity and the date the status changed.
member_id | modification_date | status
I wish to find the differences between datetime values in different rows of the modification_date column. I would need the amount of time between whatever the next sequential time was for the member. I would also need to know the status change in that time. I don't know how to do this with just one date column.
Any assistance is appreciated.
Thank you.
Calculate First And Last Dates From DATEPART
Aug 1, 2000I'm kinda stumped....
How would one find the first and and last date in a date range defined by a DATEPART, regardless of the parameter?
For Example...
DATEPART(wk,7/31/2000) would return 7/30/2000 and 8/5/2000
DATEPART(qq,7/31/2000) would return 7/1/2000 and 9/30/2000
DATEPART(yy,7/31/2000) would return 1/1/2000 and 12/31/2000
TIA,
Charles
Calculate Difference Between Two Dates
Oct 18, 2006Hi, i'm trying to calculate the number of days between two dates, but within an UPDATE statement, so far I can't wrap my head around how I can update a field with the number of days.
I was thinking something like
Code:
Update #ClaimMaster
Set covered_days = (then insert select statement that subtracts the two dates)
Does that make any sense?
Calculate Date Difference Excluding Dates
Aug 27, 2012I have already seen stored procedures that can calculate a difference in dates, excluding the weekends. Any extension of such a SQL query to exclude not only weekends, but other dates as well. We have a table of "holidays" (not necessarily standard holidays), and I am wondering if there is a way to exclude them from the calculation.
View 7 Replies View RelatedT-SQL (SS2K8) :: Calculate And Display Week Between Two Dates
Nov 4, 2015I want to display week between two dates as below.
requirement is as:
suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016
Now between this two dates, i want to calculate number of weeks on another date which is coming from table.
Say for example the column date is coming as 10/23/2015 then it will fall in week-1
Same way if 11/01/2015 falls in week2.
SQL Server 2012 :: How To Calculate Dates Difference In A Same Table
Sep 4, 2015I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
How can i do that in a query?
Calculate The Growth Percentage Or Previous Dates Values
Feb 7, 2008This code displays dates, File name, and File size for four seperate dates 11/20/2007 , 11/30/2007, 12/30/2007 and 01/31/2007 . I'm trying to show the percentage growth from date to date (ie 11/20/2007 -11/30/2007 percentage growth)
is there a way i can get the previous date file size for each entry, so i can have a variable for the calculation. Or i can get the calculate it within this code (ie database_size_mb / ((database_size_md ) where database_size_datetime -1) *100
or whatever the formula is for percentage growth.
Code Snippet
SELECT
Database_Size_Datetime,
Database_file_name,
Database_Size_Mb
FROM RC_STAT.dbo.Tbl_Database_Statistics AS Tbl_Database_Statistics_1
GROUP BY Database_Size_Datetime, Database_file_name, Database_Size_Mb
this is what it displays now:
2007-11-20 00:00:00.000 ACTReplication_Data 442.5000
2007-11-30 00:00:00.000 ACTReplication_Data 442.5000
2007-12-31 00:00:00.000 ACTReplication_Data 442.5000
2008-01-31 00:00:00.000 ACTReplication_Data 442.5000
2007-11-20 00:00:00.000 ACTReplication_Log 14.8125
2007-11-30 00:00:00.000 ACTReplication_Log 109.7500
2007-12-31 00:00:00.000 ACTReplication_Log 112.9375
2008-01-31 00:00:00.000 ACTReplication_Log 115.5625
2007-11-20 00:00:00.000 BAMArchive 0.6875
2007-11-30 00:00:00.000 BAMArchive 0.6875
2007-12-31 00:00:00.000 BAMArchive 0.6875
2008-01-31 00:00:00.000 BAMArchive 0.6875
2007-11-20 00:00:00.000 BAMArchive_log 0.4922
2007-11-30 00:00:00.000 BAMArchive_log 0.4922
2007-12-31 00:00:00.000 BAMArchive_log 0.4922
2008-01-31 00:00:00.000 BAMArchive_log 0.4922
Power Pivot :: Calculate Number Of Business Days Between Two Dates
May 14, 2015I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using the following query
DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) -
CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END +
CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END END
I am looking for a similar query in Power Pivot.
SQL Server 2008 :: Calculate Number Of Months Between Dates For Multiple Records?
Oct 7, 2015I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
Calculate Elapsed Time Between Dates And Exclude A Time Span.
Dec 18, 2007I need a formula to calculate the time (let's say in minutes) between two dates/times.
The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday).
I will use this in a couple of reports made in Reporting Services.
If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful.
Many thanks!
/Per Lissel
Want To Use Parameters To Filter For Dates Between Two (parameter, User-input) Dates
Mar 2, 2006SQL 2005 Dev
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates
Feb 28, 2015I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL
[code]...
Comparing Dates With Today Dates
Jun 21, 2005I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
View 1 Replies View RelatedSQL 7.0 And SQL 6.5 Differences
Nov 22, 1998I am curious to what major differences there are between these two versions.
Trying to decide whether or not to purchase the SQL 6.5 training kit from Microsoft or not. If the code and utilities are the same, then I could probably still learn from the 6.5 version. Any thoughts, suggestions will be greatly appreciated.
Thanks
What's The Differences?
Aug 12, 2004What is the differences between SQL Server Desktop Engine and SQL Server Standard Edition???
Thanks
Lystra
SQL CE 3.01 And 3.5 Differences
Feb 13, 2008Hi,
I am having the following problem AFTER converting to VS2008 from VS2005 and SQLCE 3.5 from 3.01:
SQL CE db file has a table called Court0 with various columns of type float. I populate the values by copying floats from another table/tables. I do this via ado.net using this code snippet:
foreach (DataColumn column in this.mycourtsDataSet1.Tables[tableName].Columns)
{
string columnName = column.ColumnName.ToString();
string columnValue = aRow[0][columnName].ToString();
object cValue = aRow[0][columnName];
if (columnName.Remove(1) == "T" && !string.IsNullOrEmpty(columnValue))
{
// Add the value to the Court0 table.
DataRow[] bRow = this.mycourtsDataSet1.Tables["Court0"].Select("BookingPeriod = '" + columnName + "'");
if (bRow.Length > 0)
{
double colValue = Convert.ToDouble(cValue);
//bRow[0][tableName] = Convert.ToInt32(columnValue);
========> bRow[0][tableName] = colValue; <==== colValue is '1055.01'
}
}
}
}
This works fine in VS2005/CE3.01 BUT not in VS2008/CE3.5
In CE3.5, the value entered into the cell looses it's decimal value.
For example, '1055.01' becomes '1055.0' in CE3.5 .
Can someone explain to me why the conversion stuffs up in CE3.5 and what do I do to fix it.
Thanks,
Inner Join Differences
Jul 4, 2006Table struct (table1 and table2):
areacode
phonenumber
phonenumber2 (combined areacode + phonenumber) (actual column)
------------------------------------------------------------
select x.areacode, x.phonenumber from table1 as x
inner table2 as y
on x.AreaCode = y.AreaCode and x.phonenumber = y.phonenumber
result: 0
select x.areacode, x.phonenumber from table1 as x
inner join table2 as y
on x.phonenumber2 = y.phonenumber2
result: 100
select (x.areacode + x.phonenumber) as phone from table1 as x
inner join table2 as y
on (x.areacode + x.phonenumber) = (y.areacode + y.phonenumber)
result: 0
WHat's the difference between those queries? Why can't I get a result from the 1st and 3rd query?
Database Differences
Feb 5, 2004We have a database that when an update is released (and this is very often) the release notes don't cover most of the actual changes. Every time groups of our custom queries and reports get broken due to database changes. Does anyone know how to compare two databases and get a report of the differences between them? I can either have the two versions on the same server or on different servers if that makes a difference.
I'm hoping for something where you input @oldversion, @newversion
and return is
@oldversion, tblname, fieldname, char(8)
@newversion, tblname, fieldname, varchar(8)
@oldversion, tblname, [Null], [Null]
@newversion, tblname, fieldname, int
@oldversion, [Null]
@newversion, tblname
also any changes in dependancies
Thanks
Brent
Rounding Differences
Feb 20, 2004I have just converted some Access VBA code to a sproc. I'm finding that for some reason the rounding is different:
eg.
ROUND(17 * 97995 / 1000,2) = 1665.915 before Rounding
SQL SProc: 1665.91 Rounds down
ADP VBA: 1665.92 Rounds up
Does this make sense?
Differences Between Dbs On Different Servers
Jul 7, 2003as promised:
--sp_addlinkedserver @server = '____________'
--sp_addlinkedserver @server = '____________'
--select * from sysservers
--sp_addlinkedserver
-- '____________',
-- 'Oracle',
-- 'MSDAORA',
-- 'ORC1'
--Select * from ___________.ORC1.dbo.sysobjects
/* Objects in Company1 Missing in Company2 */
Select 'Table Objects in Company1 but are not in Company2'
select Left(a.name,30), a.refdate from sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From ____________.dbname.dbo.sysobjects b where a.name = b.name)
/* Objects in Company2 Missing in Company1 */
Select 'Table Objects in Company2 but are not in Company1'
select Left(a.name,30), a.refdate from ____________.dbname.dbo.sysobjects a
Where a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (Select 1 From sysobjects b where a.name = b.name)
/* Column Differences */
Select 'Column Differences between like named tables'
select Left(x.TabName,30) as TableName, Left(x.ColName,30) as ColumnName
, Left(x.DataType,15) as Company1DataType, x.length as Company1Length, x.refdate as Company1RefDate
, Left(y.DataType,15) as Company2DataType, y.length As Company2Length, y.refdate as Company2RefDate
from
( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U' and a.name like 'TBL%') As x
, ( Select a.name as TabName, b.name as ColName, b.length, c.name as DataType, a.refdate
from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
where a.id = b.id and a.xtype = 'U'
and b.xusertype = c.xusertype
and a.name like 'TBL%') As y
Where x.TabName = y.TabName
and x.ColName = y.ColName
and (x.length <> y.length or x.DataType <> y.DataType)
/* Column Differences */
Select 'Column in Company1.com not in Company2'
Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from sysobjects a, syscolumns b, systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from ____________.dbname.dbo.sysobjects d, ____________.dbname.dbo.syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2
/* Column Differences */
Select 'Column in Company2 not in Company1.com'
Select Left(a.name,30) as TableName, Left(b.name,30) as ColumnName, b.length, c.name, a.refdate
from ____________.dbname.dbo.sysobjects a, ____________.dbname.dbo.syscolumns b, ____________.dbname.dbo.systypes c
where a.id = b.id
and b.xusertype = c.xusertype
and a.xtype = 'U'
and a.name like 'TBL%'
and Not Exists (
Select 1
from sysobjects d, syscolumns e
where d.id = e.id
and a.xtype = 'U'
and a.name like 'TBL%'
and a.name = d.name
and b.name = e.name)
Order by 1, 2
--Select 'Table Objects that are still in use in both Company2 and Company1'
--select Left(a.name,30), a.refdate from sysobjects a, ____________.dbname.dbo.sysobjects b
--where a.name = b.name and a.xtype = 'U'
Brett
8-)
Differences Between .sqlexpress And C:abc.mdf
Apr 2, 2008
I saw some demo-codes ,introducing sqlconnection class, sqlcommand class and etc, are involed .sqlexpress and c:abc.mdf.
so , I am quite confuse what's the deferences between .sqlexpress and c:abc.mdf
Thanks
Temp Table Differences
Jun 2, 2005What's the difference between using CREATE TABLE #TempTable and DECLARE @Table TABLE for temp tables and are there any advantages or disadvantages to using one over the other?Thanks
View 4 Replies View RelatedQuery Run Time Differences
Mar 14, 2002I have a server with two test instances of a data base. I have a query which creates a temp table, inserts 29 rows, perform 4 update queries to add counts and then dumpps out the results. This entire query script runs 1.33 minutes on one instance and 2.5 minutes on the other. On the production server this query now runs in 9 seconds. If I run any one of the test updates individually they execute under 2 seconds, just like the production server.
THe results are repeatable.
All are SQL 7 with all service packs on NT4 sp6. Both test data bases are backups of production from last week. I suspect some kind of caching/buffer problem, but I do not know what to look for. I am not a DBA so I have no idea what role TEMPDB plays may play in this.
Can anyone give us ideas on where to look for the performance difference? Will our impending upgrade to SQL2K solve this problem or make it worse? Any ideas would be appreciated.
Is There Any Tool To See The Differences In Two Databases.
Mar 29, 2002Hi,
Is there any tool to find the differences between the two databases. I would like to know the differences in developmental server and Production server. if the developers create any new objects, I want to migrate them to production server.
Can we do it in sql server 200 or do we need to have separate tool.
Thanks in advance.
Performance Differences Between Queries...
Sep 13, 2004I am updating a db with data from a file, in this data we have new info, info that has been updated and info that is to be removed from the db.
Now I was wondering which approach results in better performance/shorter executin time:
1. first update excisting values, then insert new ones, and last delete cancelled data
or
2. delete cancelled data and data that will be updated, then insert new and updated info
I get all this data from a file, in that file all rows are similar and there is one column that defines if the data is new, updated or to be deleted (thus all the updates also include the information for the enty that has not been altered).
// Pati
Differences Between 6.5 And 2000 Inserts
Oct 18, 2005Hi Guru's,
I am kind of baffeled. I have a table with a column of 8 varchar in 2000
and the same in 6.5. When I insert into 2000 with a data length of more than 8 chars via Cold Fusion into the table, it fails. The same Cold Fusion
program inserts into the 6.5 table, but truncates the data but does not fail.
Does anyone know why this happens. Thanks, Newbie.
Date Differences & CASE
Jun 3, 2008I have written the SQL below and need to change the case. I need to say that if today’s date (get date) is between <> 4 weeks of sv_latest_appraisal then = Outstanding. Can this be done in SQL? I know it is very specific so not sure. Please help! Sam
SELECT Employee.FORENAME AS Forename,
Employee.SURNAME AS Surname,
Employee.LOCATION AS Location,
Employee.DEPARTMENT AS Department,
Employee.STARTDATE AS Startdate,
Sv_latest_appraisal.NEXT_APP AS Next_app,
Sv_latest_appraisal.USR_EAR_TYPE AS Usr_ear_type,
Sv_latest_appraisal.USR_EAR_TYPE_NEW AS Usr_ear_type_new,
CASE
WHEN Sv_latest_appraisal.NEXT_APP <= getdate() THEN 'OVERDUE'
WHEN Sv_latest_appraisal.NEXT_APP >= getdate() THEN 'NOT DUE'
WHEN Sv_latest_appraisal.NEXT_APP = getdate() THEN 'DUE TODAY'
ELSE 'UNKNOWN'
END
FROM (dbo.EMPLOYEE AS Employee
INNER JOIN dbo.SV_latest_appraisal
ON Employee.EMPLOY_REF = Sv_latest_appraisal.EMPLOY_REF)
INNER JOIN dbo.JOB AS Job
ON Employee.JOB_REF = Job.JOB_REF
WHERE (Employee.LEAVER = 0)AND (Employee.LOCATION LIKE 'GE')
Values Differences Between Days?
Jan 22, 2015I have a SQL Server 2005 table titled "Journeys" as follows:
+---------------+
| Journeys |
+---------------+
| JourneyID |
| PlateNumber |
| DepartureDate |
| DepartureKM |
| ArrivalDate |
| ArrivalKM |
+---------------+
The table contains the following sample data:
+------------+--------------+----------------+--------------+--------------+-----------+
| JOURNEYID | PLATENUMBER | DEPARTUREDATE | DEPARTUREKM | ARRIVALDATE | ARRIVALKM |
+------------+--------------+----------------+--------------+--------------+-----------+
| 1 | ABC-123 | 01-01-2015 | 10000 | 01-02-2015 | 10200 |
| 2 | ABC-123 | 01-02-2015 | 10210 | 01-03-2015 | 10500 |
| 3 | ABC-123 | 01-03-2015 | 10500 | 01-04-2015 | 10650 |
| 4 | ABC-123 | 01-04-2015 | 10607 | 01-05-2015 | 10900 |
| 5 | XYZ-999 | 01-15-2015 | 30200 | 01-16-2015 | 30400 |
| 6 | XYZ-999 | 01-16-2015 | 30405 | 01-17-2015 | 30600 |
| 7 | XYZ-999 | 01-17-2015 | 30600 | 01-18-2015 | 30750 |
| 8 | XYZ-999 | 01-18-2015 | 30752 | 01-19-2015 | 30920 |
+------------+--------------+----------------+--------------+--------------+-----------+
I want to generate a query that returns a the following results with an extra column named 'KMDifference' which is the difference between 'ArrivalKM' from last day and 'DepartureKM' from today.
Desired results:
+-------------+---------------+-------------+-------------+-----------+--------------+
| PlateNumber | DepartureDate | DepartureKM | ArrivalDate | ArrivalKM | KMDifference |
+-------------+---------------+-------------+-------------+-----------+--------------+
| ABC-123 | 01-01-2015 | 10000 | 01-02-2015 | 10200 | 0 |
| ABC-123 | 01-02-2015 | 10210 | 01-03-2015 | 10500 | 10 |
| ABC-123 | 01-03-2015 | 10500 | 01-04-2015 | 10650 | 0 |
| ABC-123 | 01-04-2015 | 10607 | 01-05-2015 | 10900 | 7 |
| XYZ-999 | 01-15-2015 | 30200 | 01-16-2015 | 30400 | 0 |
| XYZ-999 | 01-16-2015 | 30405 | 01-17-2015 | 30600 | 5 |
| XYZ-999 | 01-17-2015 | 30600 | 01-18-2015 | 30750 | 0 |
| XYZ-999 | 01-18-2015 | 30752 | 01-19-2015 | 30920 | 2 |
+-------------+---------------+-------------+-------------+-----------+--------------+
[URL]
Find All Differences Between 2 Tables
Mar 5, 2015I have this 2 tables:
Table1: Table2:
ID Value ID Value
--------- ---------
1 2 2 1
2 1 3 4
4 3 4 4I
need to find all differences between the 2 Tables.
A mismatch can be:
- Table1.ID exists, Table2.ID does not exist
- Table2.ID exists, Table1.ID does not exist
- Table1.ID = Table2.ID and Table1.Value <> Table2.Value
So I want to be returned:
ID Value ID Value
--------------------
1 2 NULL NULL
4 3 4 4
NULL NULL 3 4
There must be something simpler than the solution I came up with which is:select * from Table1 a full outer join Table2 b on a.Id = b.Id
WHERE a.value <> b.value or (a.value IS NULL and b.value IS NOT NULL) or (a.value IS NOT NULL and b.value IS NULL)Martin
Anyone Compare The Differences BTN DIFF SQL
Dec 6, 2005HEY,
CAN YOU TELL ME THE DIFFERENCES BETWEEN VARIOUS SQL