FAQ Database Discussion Community


R - Grouping Dates and Summarizing Data for repeat purchase

r,group-by,datediff,date-difference
I am trying to do analysis on the repeat purchase behavior of the customers. My goal is two-fold: Eliminate all the customer item zip combination that occurs only once (eliminate the non-repeating ones) For the records that repeat, I want to summarize the same where I need to get the...

Multiple Vars for DateDiff VBA

excel,vba,datediff
I have a VBA script at the moment that reads in 6 values as integers. 3 values for date, month, and year and 3 of the same type from a different location. I'd like to take the values and check if there has been more than a year to pass...

avoiding duplicate rows SQL Server

sql-server,duplicate-removal,datediff
Could you please suggest how can I avoid duplicate values for the below scenario. I am using a datediff function for which my second parameter is getting more than one row and is causing an error. SELECT COALESCE(DATEDIFF(DAY,'1987-01-19 00:00:00.000',( SELECT CASE WHEN B.BILLDATE IS NULL THEN COALESCE(MIN(B.DUEDAY),0) ELSE COALESCE(MIN(B.BILLDATE),0) END...

how to get the time interval of a date and a datetime displayed as day,hour, min,seconds in sql

mysql,datetime,datediff
I want to get the difference of a date and a datetime and display it as elapse time, example: 4days 7hr 8min 3sec.. Have tried this but only gives me the difference of the date part: SELECT a.id, b.creation_date ,c.user_name,d.operation,d.system_,e.name, e.email_address,e.office,(coalesce(s.id, 0)) as status,t.status as status_name,b.attachment, date_format(a.date ,'%Y-%m-%d') as dateTakenStage,...

DATEDIFF function in Oracle

oracle,datediff
I need to use Oracle but DATEDIFF function doesn't work in Oracle DB. How to write the following code in Oracle? I saw some examples using INTERVAL or TRUNC. SEELECT DATEDIFF ('2000-01-01','2000-01-02') AS DateDiff; ...

php date->diff() returning nonsensical amounts

php,datetime,laravel,datediff
This is my code: $logdate = Input::get('logdate'); $enddate = Input::get('enddate'); //Cast the dates to yyyy-dd-mm format for comparison $startdt = date('Y-d-m',strtotime($logdate)); $enddt = date('Y-d-m',strtotime($enddate)); //Cast the dates into datetime objects $date1 = new DateTime($startdt); $date2 = new DateTime($enddt); //Calculate the difference between the 2 datetime objects $diff = $date1->diff($date2, true);...

How to get the count of field of last 1 week ,last 1 month ,yesterday

mysql,datediff
I want to get count of customers city wise of today ,yesterday ,last week ,last month . I want Result like city today yesterday lastweek lastmnth 1 23 2 12 12 I have my table structure like the following customers c_id city_id c_name currentdate 1 1 Rama 2015-01-30 09:43:17 2...

MySQL query with DATEDIFF

mysql,sql,datediff
I am trying to build a query that will load records from and to specific date comparing 2 fields - the start_time and the end_date. SELECT start_time ,end_time ,DATEDIFF(end_time, start_time) AS DiffDate FROM my_tbl WHERE start_time >= '2015-04-27 00:00:00' AND end_time <= '2015-04-28 00:00:00' AND end_time >= '2015-04-27 00:00:00' AND...

Querying the exact years in between two other dates (MS SQL)

sql-server-2008,case,datediff,datepart
I am struggling with an assignment and I am not sure if there is a solution. To be clear: I am not looking for the amount of years in between(datediff()). I have to join two tables: Table 1 with attributes: ID P_Startyear P_Endyear Table 2 with attributes: ID B_Year There...

VBScript echo every 30 seconds

datetime,vbscript,datediff
The following code writes to the screen every iteration. Based on my understanding of the DateDiff documentation, it should only write every 30 seconds. What did I do wrong? lasttime = Now Do While Not data.eof 'looping through database records if DateDiff(s,lasttime,Now) >= 30 Then lasttime = Now WScript.Echo "It's...

problems in comparing two dates in php, minutes always 0

php,datetime,datediff
I'm gonna need to set up same date comparisons and I'm practicing first with this php code, however no matter how I alter the date I end up getting the hours and seconds but the minutes is always 0. Btw, ideally I would like to be able to write the...

Trigger with a RAISERROR and ELSE case issue

sql-server,triggers,datediff,raiserror
I am trying to make a bit of code that takes in 2 separate columns, a month and a year. From there I want it to see if those numbers entered have already passed or not. If they have passed, cause an error to pass and stop the transaction. Otherwise,...

DateDiff to show Minutes and Seconds

sql-server-2008-r2,datediff
I need to calculate the diff and have my answer show minutes and seconds. Here is sample Declare @clockin datetime, @clockout datetime, @total decimal(18,4) Set @clockin = '2015-01-03 08:15:19.000' Set @clockout = '2015-01-03 12:02:42.000' Set @total = DateDiff(minute, @clockin, @clockout) But this returns a whole number I need it to...

How do I do this monthly calculation taking into account the correct number of months in SQL?

sql,sql-server,datediff
DECLARE @InputPeriodStart DATE = '1/1/2014' DECLARE @InputPeriodEnd DATE = '12/31/2014' ROUND(CONVERT(DECIMAL, DATEDIFF(dd, @InputPeriodStart, @InputPeriodEnd)) / 30, 1) AS DECIMAL(18, 2)) The issue here is that not every month has 30 days in it. So how can I make this calculation work properly? I would need to remove the ROUND() and...

Trying to convert total minutes from a datediff to days, remaining hours, & minutes in Crystal Reports

crystal-reports,datediff
I am using Crystal Reports 2013 SP5 on Windows 7 Pro pulling from an Oracle db. I am trying to write a formula(s) in crystal reports that returns the total number of days the remaining hours if less than 24, and the remaining minutes if less than 60 from a...

ColdFusion DateDiff - Is there a better way?

mysql,coldfusion,datediff,simplify
I simply want to combine the total years of service from various members of my team. The below script works fine, however, the hire date for each member does not pull from a database. Which means I have to hard code each persons hiredate and then do a DateDiff on...

Retrieve two records within the same table and get datediff in one query

sql-server,datediff
I have this table sessionid | serviceid | requesttimestamp | etc.. My aim is to get a serviceid for logging in and logging out which for arguments sake we will say is 2 and 3. So when the two records have a log in and log out then I want...

Calculate DiffDays function DateTime order - Does the larger DateTime come first or second

c#,linq-to-sql,asp.net-mvc-5,linq-to-entities,datediff
I am coding a MVC 5 internet application, and I am wanting to write a function that will work with both a SQL statement as well as with a List. Here is my code: public static class TestableDbFunctions { [System.Data.Entity.DbFunction("Edm", "DiffDays")] public static double? DiffDays(DateTime? dateValue1, DateTime? dateValue2) { if...

Finding the interval between dates in SQL Server

sql,sql-server,datediff
I have a table including more than 5 million rows of sales transactions. I would like to find sum of date intervals between each customer three recent purchases. Suppose my table looks like this : CustomerID ProductID ServiceStartDate ServiceExpiryDate A X1 2010-01-01 2010-06-01 A X2 2010-08-12 2010-12-30 B X4 2011-10-01...

How to count login hours between logging on and off multiple times in a day in sql

sql,datetime,datediff
I am a beginner at SQL. I have rows for logon and log off where I can see the time between each log on and log off. I would like to count how many hours a user was logged on. select oneAA.DT, oneAA.first_name, oneAA.surname, oneAA.time, oneAA.name, oneAA.incID, --now add the...

I want get record after 4 days of last_update?

mysql,datediff
I have tried but not getting a expected result. Following is MySQL query: SELECT * FROM tbl_order_final WHERE od_last_update >$current_date' AND DATEDIFF( DATE( od_last_update ) , $current_date ) = 4 ...

Access VBA getting DateDiff results to work with Update SQL statement

vba,ms-access,access-vba,datediff
I'm using Access 2007 by itself with no connections to SQLserver or anything for this process. I want to take the result of a few DateDiff functions and use an Update SQL statement to put them into fields on a table. My table's fields are number fields, and I am...

Get Exact Number of Days Between Certain Months Using DATEDIFF() Not Giving Expected Results

sql-server,datediff
The following is sample data: NewReportDate | Age | OldReportDate ----------------------------------------- 2014-04-28 |31.558767 | 2014-03-28 2014-04-28 |13-438767 | 2014-03-28 2014-04-28 |13.198767 | 2014-03-28 The following is my query: SELECT Q.Type ,Q.Oper ,Q.SNo ,Q.Hours ,CONVERT(Date, Q.ReportDate + '28', 112) AS 'Report_Date' , a.* , (DATEDIFF(dy, CONVERT(Date, Q.ReportDate + '28', 112),A.ReportDate )/365)...

MySQL datediff strange results

mysql,datediff
I am getting weird results for the datediff function. It's zero for different and same timestamps? select datediff('2015-04-25 20:37:45','2015-04-25 05:20:00'); +-------------------------------------------------------+ | datediff('2015-04-25 20:37:45','2015-04-25 05:20:00') | +-------------------------------------------------------+ | 0 | +-------------------------------------------------------+ select datediff('2015-04-25 20:37:45','2015-04-25 20:37:45'); +-------------------------------------------------------+ |...

convert number of days into years,months,days

c#,asp.net,vb.net,datediff,days
I want vb.net code to convert number of days into years,months,days. Example: A Employee Experience to be calculated as per his Date of join(DOJ) and date of Relieve(DOR). We have a DOJ,DOR and Number of Days he is worked as a employee. Have to Calculate the How many Years and...