Date-difference will give difference in the number of days, months, years etc. between any two calendar dates. Not defined by ISO/ANSI SQL, but several vendors have similar implementations.
Questions tagged [datediff]
1599 questions
4
votes
1 answer
PIG subtract two dates
I'm trying to subtract two dates with PIG.
I have such data:
key_one, activation_date , deactivation_date
(1456,2010-06-14 00:00:00,2011-01-01 00:00:00)
(6524,2015-01-15 00:00:00,2015-02-07 00:00:00)
(1541,2010-07-17 00:00:00,2012-03-07…
Marta
- 3,493
- 6
- 28
- 43
4
votes
2 answers
MS Sql Datediff Function
I tried to write a ms sql code that gives you workers work time in minutes. It takes two input as date. But it always return 0. What did i do wrong?
Create FUNCTION getMinutesWork
(
@inp1 date,
@inp2 date
)
RETURNS int
As
Begin
…
calcuku
- 67
- 6
4
votes
2 answers
Calculating time difference by ID
I have data like this:
Incident.ID.. = c(rep("INCFI0000029582",4), rep("INCFI0000029587",4))
date = c("2014-09-25 08:39:45", "2014-09-25 08:39:48", "2014-09-25 08:40:44", "2014-10-10 23:04:00", "2014-09-25 08:33:32", "2014-09-25 08:34:41",…
ElinaJ
- 791
- 1
- 6
- 18
4
votes
1 answer
DATEDIFF command won't work as 'day' is not a recognised column
I'm relatively new to SQL and have been attempting to run a script wherein I can bring up the number of days that have passed between two points in time. I understand how this should look based on your website, but for some reason when I input the…
CSP
- 49
- 1
- 2
4
votes
4 answers
Is there a way to subtract an amount of days from a date in SQL?
I know about DATEDIFF(d, date1, date2), but I am not looking to subtract two dates, rather an amount of days from a date.
For example:
"2010-04-13" - 4 = "2010-04-09"
Is that possible with mySQL?
Alex
- 75
- 1
- 5
4
votes
3 answers
Is SQL DATEDIFF(year, ..., ...) an Expensive Computation?
I'm trying to optimize up some horrendously complicated SQL queries because it takes too long to finish.
In my queries, I have dynamically created SQL statements with lots of the same functions, so I created a temporary table where each function is…
rlb.usa
- 14,942
- 16
- 80
- 128
4
votes
6 answers
Get records based on Current Date and Configured Data
I am working on automating the SMS sending part in my web application.
SQL Fiddle Link
DurationType table stores whether the sms should be sent out an interval of Hours, Days, Weeks, Months. Referred in SMSConfiguration
CREATE TABLE…
Billa
- 5,226
- 23
- 61
- 105
4
votes
1 answer
How do I get the Average number of days between multiple dates in Tableau
I'm using Tableau and I have a field called Sales_Order_Date. I need to get the AVERAGE number of days between each sales order.
Example:
I have 3 orders
01/01/2014
20/01/2014
30/01/2014
The number of days between order 1 and order 2 = 19 days
The…
StefanHanotin
- 191
- 5
- 17
4
votes
4 answers
MySQL - Getting age and numbers of days between two dates
I am trying to query a huge database (aprroximately 20 millions records) to get some data. This is the query I am working on right now.
SELECT a.user_id, b.last_name, b.first_name, c.birth_date FROM users a
INNER JOIN users_signup b ON a.user_id a =…
Cryssie
- 3,047
- 10
- 54
- 81
4
votes
1 answer
Time difference between dateTime fields SSRS
I have two datetime fields and I need to show the difference between them. I've used this expression to calculate it:
=DateDiff("n", Fields!hra_atncion.Value, Fields!fcha_incio.Value)
The result column should be on HH:mm, but the result is a Long…
joisman
- 89
- 2
- 10
4
votes
3 answers
SQL(?): Counting the time between two datetime values
What is the best way to count the time between two datetime values fetched from MySQL when I need to count only the time between hours 08:00:00-16:00:00.
For example if I have values 2008-10-13 18:00:00 and 2008-10-14 10:00:00 the time difference…
Samuli Vainio
4
votes
1 answer
SQL Datediff with group by
I have a table called Login. I want to get the No. of days from the last login per user
See sample data below:
tableName: Login
ID|UserName|LoginDateTime
1 |User1 |04/09/12 18:07:06
2 |User1 |04/09/12 18:07:51
3 |User1 |04/09/12…
user2617053
- 323
- 3
- 5
- 13
4
votes
2 answers
Javascript: Milliseconds to a date
How would I go about to get the amount of milliseconds and a specific data. For Instance, i know how to create a date:
var d = new Date(2014,02,22,12,0,0,0);
and to get today's date:
(new Date()).getTime() + 10*24*60*60*1000;
But now how…
Corne
- 496
- 2
- 8
- 22
4
votes
3 answers
What is the most accurate way of using DATEDIFF in SQL Server?
I have two computed columns (MonthsInService and YearsInService) with the following expressions.
MonthsInService = (datediff(month,[DateEngaged],getdate()))
YearsInService = (datediff(month,[DateEngaged],getdate())/(12))
Now if for example…
StackTrace
- 9,190
- 36
- 114
- 202
4
votes
2 answers
SQL query to get DateDiff of the last two records
I have a table called Event with eventNum as the primary key and date as a datetime2(7) in SQL Server 2008 R2. I am trying to get the date of the last two rows in the table and get the difference in minutes. This is what I currently have:
Select…
cs_erik
- 41
- 1
- 3